topic Re: Display one single record in QlikView

Display one single record

Tue, 16 Apr 2013 11:51:00 GMT

I have a table with columns A, B, C, D, E, F and G.

Now I want to display just one record for specific value in A in such a way other filters on the same sheet do not work on this.

Re: Display one single record

Tue, 16 Apr 2013 13:28:42 GMT

That's a tough one. I've had similar-sounding problems and had to resort to lengthy set expressions. Can you give a little more detail as to what the situation is?

Re: Display one single record

Tue, 16 Apr 2013 14:46:41 GMT

Sure,

I have table (excel sheet uploaded in Qlikview) that contains many data fields.

This problem focuses on few data fields - A,B,C,D and E.

Column A is basically a scenario column (where value 1 is default scenario value) and other columns are regular number data fields.

Sample data looks like this:

A	B	C	D	E
1	7	12	0.67	1
1	2	5	0.3	3
1	9	7	0.49	4
1	9	7	0.73	5
2	9	9	0.2	3
2	1	5	0.31	3
2	4	17	0.55	2
2	7	13	0.66	3
3	2	17	0.24	1
3	9	6	0.51	3
3	7	2	0.95	2
3	1	12	0.48	2

There will be a scenario drop down list that contains different column A values, in this example it will be 1,2,3 where 1 is default value.

This drop down list will act as a filter and we will get all the values for the specific scenario that we select so far its easy.

But for the purpose of comparing different scenario's I need to display default scneario values that do not change with filter selection i.e. default scenario (Column A value =1) should always be displayed irrespective of scenario filter we apply.

Please find the QlikView file.

Re: Display one single record

Tue, 16 Apr 2013 16:02:52 GMT

That's actually relatively simple! Just replace your sum(B) with Sum({<A={1}>}B), sum(C) with =Sum({<A={1}>}C), etc. The Qlikview help tab has an excellent page on Set Analysis that I recommend reading for similar problems.

Re: Display one single record

Wed, 17 Apr 2013 09:11:43 GMT

Will it be possible if you can explain me the logic behind this expression.

thanks

Re: Display one single record

Wed, 17 Apr 2013 12:33:17 GMT

This is pretty basic set analysis. Again, I can't stress strongly enough how good an idea it is to click 'help' and type 'Set Analysis' and then read the help info that Qlikview provides.

Basically, the outer {} is a set statement. It is telling Qlikview to 'compute this equation where the statement inside the {} is true'. <> is a subset statement. It allows you to chain together multiple set statements. The most common example of this is Sum({$<A={1}>}C), which is 'The sum of all C where A = 1 and '$', which means 'current selections apply'.

The inner {} is another set statement, but in this case it's the possible values that A can take. If you wanted to take the sum of A=1 and A=2, it'd be Sum({$<A={1,2}>}C). Hope this helps!