Tagging max number by Id

[#1]

Hi Everyone,

I want to tag the ID with the max of number visit, but QV give me the error 'Invalid expression' .
So, what i have tried is this:

SCRIPT1

Tab:

LOAD Id,

     number_visit,

     max(number_visit) as Max_number_visit,

     if(number_visit < max(number_visit), 0, 'X') as Max_number_Tag

from Tab1

SCRIPT2

Tab:

LOAD Id,

     number_visit,

     max(number_visit) as Max_number_visit,

     if(max(number_visit),'X', 0) as No_visit_Tag

from Tab1

Both the above  scripts i have tried are not working for me!

My Result should lool like this:

ID	number_visit	No_visit_tag
A	1	0
A	2	0
A	3	X
B	1	0
B	2	0
B	3	0
B	4	X
C	1	0
C	2	X

PLEASE HELP!

[whiteline]

Hi.

It's a bit strange that you're trying to use Max() aggregation function without 'group by' statement.

Another thing that you use 'from' statement instead of 'resident'.

What is your goal MaxNumber or Tag ?

left join(Tab1)

LOAD

     Id,

     max(number_visit) as Max_number_visit

Resident Tab1

Group by  Id;

or

left join(Tab1)

LOAD

     Id,

     max(number_visit) as number_visit,

     'X' as No_visit_tag

Resident Tab1

Group by  Id;

[#3]

thanks for the reply whiteline  but i have used group by at the end also. I want to tag the visit number where it is maximum. So i know this is the last visit

[whiteline]

Have you tried second load provided above ?

[#5]

the second load will only provide me with the field 'No_visit_tag' with all the  'X' in that

[whiteline]

The second load will create a table:

A 3 X

B 4 X

C 2 X

and join it to your original table using two keys (id and number_visit), so that you'll have:

A     1 -

A     2 -

A     3     X

B     1 -

B     2 -

B     3 -

B     4     X

C     1 -

C     2     X

[#7]

Hi whiteline,

our goal is to tag all the ID's with the max Visit number.

and  by creating a list box and select  X from the list box to have  just all Id's where number_visit is maximum.

Can you give me detail script example what you mean because I have tried your idea above and still not working.

I tried:

Tab1:

LOAD

     Id,

     number_visit,

from Tab1;

left join

LOAD

Id,

if(max(number_visit), 'X') as Max_number_Tag

Resident Tab1

group by Id;

Our table now is look like this:

ID	number_visit	Max_number_Tag
A	1	X
A	2	X
A	3	X
B	1	X
B	2	X
C	1	X
C	2	X
C	3	X

But the correct table should look like the solution table from above.

[whiteline]

You're joining using Id as a key.

My idea is completely different, join using Id and number_visit as keys:

left join(Tab1)

LOAD

     Id,

     max(number_visit) as number_visit,

     'X' as No_visit_tag

Resident Tab1

Group by  Id;