topic Re: set analysis for values not equal in QlikView

set analysis for values not equal

Anonymous — Thu, 18 Jun 2015 20:28:48 GMT

I currently have a table with some fields that are usually equal, but sometimes are not. I would like to create another table below this one that shows only those records such that those values are not equal. ie the current selection shows me

ID	quantity 1	quantity 2
1	5	5
2	6	7
3	7	12

and in the next table I want it to show

ID	quantity 1	quantity 2
2	6	7
3	7	12

I'm teaching myself qlikview and just trying to do this as an exercise. Can I use set analysis for this?

Re: set analysis for values not equal

Anonymous — Thu, 18 Jun 2015 22:15:07 GMT

I don't think Set Analysis will work for what you are trying to achieve because it evaluates once on the entire data set.

Something like count(IF([quantity 1] <> [quantity 2],ID)) as an expression in a chart would work.

Re: set analysis for values not equal

Anonymous — Thu, 18 Jun 2015 22:31:16 GMT

If you want to do this with set analysis I would add something like this in the script:

Load ID,

quantity1,

quantity2,

IF(quantity1 = quantity2, 1,0) as Same

Resident data;

Now you can use the field Same in your set analysis.

But you can also create a new dimension, like:

Load ID,

quantity1,

quantity2,

IF(quantity1 <> quantity2, ID) as ID2

Resident data;

If you use ID2 as your dimension it should give you the result you are looking for

ID2	quantity 1	quantity 2
2	6	7
3	7	12

If possible I would (personaly) always go for a solutions without set analysis.