topic Re: Set Analysis Expr in QlikView

Set Analysis Expr

Anonymous — Wed, 29 Oct 2014 10:50:10 GMT

Hi,

I have 2 date fields in my datamodel:

1 date field value is having multiple 2nd date field value as shown below:

DateField1 DateField2

24/07/2014	24/07/2014
24/07/2014	25/07/2014
24/07/2014	29/07/2014
24/07/2014	04/08/2014
25/07/2014	24/07/2014
25/07/2014	25/07/2014
25/07/2014	29/07/2014
25/07/2014	04/08/2014
29/07/2014	24/07/2014
29/07/2014	25/07/2014
29/07/2014	29/07/2014
29/07/2014	04/08/2014
04/08/2014	24/07/2014
04/08/2014	25/07/2014
04/08/2014	29/07/2014
04/08/2014	04/08/2014
11/08/2014	24/07/2014
11/08/2014	25/07/2014
11/08/2014	29/07/2014
11/08/2014	04/08/2014

Now the requirement is when i use the datefield1 as the dimension and i need to get the below output for datefield2 in a straight table

Datefield1	DateField2
24/07/2014	24/07/2014
25/07/2014	25/07/2014
29/07/2014	29/07/2014
04/08/2014	04/08/2014
11/08/2014	null

Re: Set Analysis Expr

DavidFoster1 — Wed, 29 Oct 2014 10:52:13 GMT

What is the logic you want to use. There seems to be a mix of MIN, MAX and something else for the 11th

Re: Set Analysis Expr

anbu1984 — Wed, 29 Oct 2014 10:54:52 GMT

Check this qvw

Re: Set Analysis Expr

rubenmarin — Wed, 29 Oct 2014 10:55:29 GMT

Maybe it's just an if:

If(DateField2=DateField1, DateField2)

I think you can't use set analisys to make that comparison (a different value for each row)

Re: Set Analysis Expr

Anonymous — Wed, 29 Oct 2014 10:57:46 GMT

For each datefield1 value- i need the matching datefield2 value,if the matching value is not there then it should be null.

Both the Datefield1 and Datefield2 values should be same if there exists a value for Datefield2,

Re: Set Analysis Expr

Anonymous — Wed, 29 Oct 2014 11:04:59 GMT

Thanks anbu.It worked as expected

Re: Set Analysis Expr

dferreirogft — Wed, 29 Oct 2014 11:05:37 GMT

@Ruben Marin's Solution will work but I will advise you to modify the script and to create a new column with a flag when the two dateFields are matching:

LOAD *

if(DateField2=DateField1,1,0) as Flag

from [...];

Then you can write your set analysis like

sum({<Flag={1}>}fieldToSum)

This way you will follow Qlikview guidelines and the performance will be better.

Cheers

Dany