topic Re: Set Analysis related Query in QlikView

Set Analysis related Query

Wed, 18 Mar 2015 07:45:56 GMT

Dear Team,

i have Date, Session IP fields in my straight table. my requirement is to get session wise IP of the max Date.

below is the output table

SESSIONID	Max Date IP	MAX(DATE)	=16
		16	16
100000		14	16
100002	10.109.39.95	16	16
100003	10.109.39.245	16	16

Issue is when i use Max(DATE) statement into Exprerssion its return the max Date, i have used the set analysis to get the max Date IP. the Set analysis is as below

=ONLY( {<DATE={'$(=MAX(DATE))'}>} IP)

but the max(date) returning me 16 DATE only. so those have max Date 16 that is working file but for those who have 14 max date they have problem.

below is the sample table also.

DATE	IP	SESSIONID
11	10.109.39.108	100000
12	10.109.39.108	100000
13	10.109.39.108	100000
14	10.109.39.108	100000
11	10.109.39.95	100002
12	10.109.39.95	100002
13	10.109.39.95	100002
16	10.109.39.95	100002
11	10.109.39.233	100003
11	10.109.39.245	100003
12	10.109.39.233	100003
12	10.109.39.245	100003
13	10.109.39.233	100003
13	10.109.39.245	100003
14	10.109.39.233	100003
14	10.109.39.245	100003
16	10.109.39.245	100003

Re: Set Analysis related Query

anbu1984 — Wed, 18 Mar 2015 08:22:33 GMT

Set analysis works at table level and not at row level. So in this expr =ONLY( {<DATE={'$(=MAX(DATE))'}>} IP) Max(DATE) always return 16, so you got result for sessionIds where your max(DATE) is 16 and for remaining sessionIds null value

Try this

=FirstSortedValue(IP,-DATE)

Re: Set Analysis related Query

Wed, 18 Mar 2015 08:43:22 GMT

This is only working only when Date Session and IP is not repeating. i just shared the sample and selected field only, i have more fields like DateTime etc. so when the same date IP repeat this not working.

Re: Set Analysis related Query

Wed, 18 Mar 2015 08:46:17 GMT

Apart from this my Question is When i put Max(date) into expression its return me the max(date) of that particular Deminsion only.

So how can i write Max(date) function into set analysis so that i can get the same result set.

Re: Set Analysis related Query

rubenmarin — Wed, 18 Mar 2015 08:59:09 GMT

Hi Jitendra, if you have a table with SESSIONID as dimension, you can set this expression:

Aggr(If(DATE=Max(TOTAL <SESSIONID> DATE), IP), SESSIONID, DATE)

Re: Set Analysis related Query

Wed, 18 Mar 2015 09:07:45 GMT

this is failing when the same max date have more than one IP .

Re: Set Analysis related Query

tresB — Wed, 18 Mar 2015 09:10:51 GMT

Try:

=FirstSortedValue(Distinct IP,-DATE) // Ignore the warning in the syntax when you try. It works fine.

Re: Set Analysis related Query

rubenmarin — Wed, 18 Mar 2015 09:19:35 GMT

Hi Jitendra, if can be more than one IP by sessionid in the same date, the IP should be a dimension, so it's possible to give more than one value for each SESSIONID.

You can set SESSIONID as first dimension and second dimension can be:

=Aggr(If(DATE=Max(TOTAL <SESSIONID> DATE), IP), SESSIONID, DATE, IP)

Check supress null values in this dimension.

You'll need to create an expression, for testing purposes you can create an expression like =' '

Re: Set Analysis related Query

Wed, 18 Mar 2015 09:23:06 GMT

Thanks to Everyone for such active response and support