topic Re: Comparing two field in set analysis in QlikView

Comparing two field in set analysis

Anonymous — Fri, 04 Sep 2015 08:36:18 GMT

Hello Guys,

Can we compare two field in set analysis?

I have seen some earlier posted question but no proper response.

Example: I am having two fields say OpenData, MISDate with Amount.

Can I write some thing in SET like =sum({<OpenDate=MISDate>} Amount)??

I tried with IF, it is working but not with set analysis.

Pls suggest.

Regards,

Balraj Ahlawat

Re: Comparing two field in set analysis

alexandros17 — Fri, 04 Sep 2015 08:38:53 GMT

No you cannot, you can use Field = Value where this value can be a specific field single value but not the entire set of values.

Re: Comparing two field in set analysis

Anonymous — Fri, 04 Sep 2015 08:43:19 GMT

may be try:

sum({<Date={"=OpenDate=MISDate"}>}Amount)

Re: Comparing two field in set analysis

Anonymous — Fri, 04 Sep 2015 08:49:03 GMT

try this

sum({<OpenDate={'=MISDate'}>} Amount)

Re: Comparing two field in set analysis

sasiparupudi1 — Fri, 04 Sep 2015 09:14:59 GMT

Can you please post a sample?

Re: Comparing two field in set analysis

tresB — Fri, 04 Sep 2015 09:19:06 GMT

Try like:

sum({<OpenDate={"=OpenDate=MISDate"}>}Amount)

Re: Comparing two field in set analysis

Anders_Eriksson — Fri, 04 Sep 2015 09:27:34 GMT

This is a case for the P() function in set analysis;

sum({$<OpenDate=P(MISDate)>} Amount)

Match filter field OpenDate on the possible dates acquired from the MISDate field.

But this will match all OpenDate to all MISDate.

If you want only those where OpenDate = MISDate on the exact data post you need a different approach.

I would probably add a field when loading data where I store if the dates are equal and use that field in set analysis.

Re: Comparing two field in set analysis

Kushal_Chawda — Fri, 04 Sep 2015 09:31:20 GMT

sum({<OpenDate={"=OpenDate=MISDate"}>} Amount)

sum(if(OpenDate=MISDate,Amount))

sum({<OpenDate=p(MISDate)>}Amount)

Re: Comparing two field in set analysis

Anders_Eriksson — Fri, 04 Sep 2015 11:36:59 GMT

Another way of doing that would solve your problem is:

sum( -(OpenDate=MISDate) * Amount)

The comparison yields 0 if uneqal and -1 if equal dates.

Negating that you get 1 for equal dates, 0 for unequal dates

which you then can use as a simple factor in a multiplication.

This does away with the if() function which is notorious for taking

resources when computing and should be avoided if possible.

Doing that I would probably add a comment to the code explaining what it does.

For someone experienced in C-programming it is nothing strange.