topic Re: Tagging max number by Id in QlikView

Tagging max number by Id

Wed, 19 Sep 2012 10:53:57 GMT

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!

Re: Tagging max number by Id

whiteline — Wed, 19 Sep 2012 11:19:45 GMT

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;

left join(Tab1)

LOAD

Id,

max(number_visit) as number_visit,

'X' as No_visit_tag

Resident Tab1

Group by Id;

Re: Tagging max number by Id

Wed, 19 Sep 2012 11:23:07 GMT

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

Re: Tagging max number by Id

whiteline — Wed, 19 Sep 2012 11:30:49 GMT

Have you tried second load provided above ?

Re: Tagging max number by Id

Wed, 19 Sep 2012 11:34:49 GMT

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

Re: Tagging max number by Id

whiteline — Wed, 19 Sep 2012 11:39:45 GMT

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

Re: Tagging max number by Id

Wed, 19 Sep 2012 13:44:57 GMT

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.

Re: Tagging max number by Id

whiteline — Wed, 19 Sep 2012 16:21:04 GMT

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;