topic Re: Expressions using values of different rows from same datatable in QlikView

Expressions using values of different rows from same datatable

Thu, 27 Nov 2014 15:27:20 GMT

Hi all

my datatable looks like this

SKU	PrimarySKU	Availability	Volume
A	A	5	30
B	A	7	20
C	A	9	100
D	D	3	10
E	E	1	5

I am trying to create a pivot chart showing

SKU	Availability	Volume	PrimarySKU	Avail. PrimarySKU	VolumeOccupied	Exp VolOccupied
A	5	30	A	5	30*5	30*5
B	7	20	A	5	20*7	20*5
C	9	100	A	5	100*9	100*5
D	3	10	D	3	10*3	10*3
E	1	5	E	1	5*1	5*1

Basically the SKU field is leading and for some of these, there is a common primary SKU

I would like to have some expressions that are calculations of fields directly related to SKU field (like VolumeOccupied, easy)
I also would like to calculate based on values linked to PrimarySKU field, like Avail. PrimarySKU. This is more difficult for me.

Or even a mixture of the two, so values linked to SKU together with values linked to PrimarySKUm as in Exp VolOccupied.

Any idea how to realise last two expressions?

thank you!

Re: Expressions using values of different rows from same datatable

demoustier — Thu, 27 Nov 2014 15:56:52 GMT

Hi,

a solution in attached file with ApplyMap function

Kind regards

Re: Expressions using values of different rows from same datatable

Thu, 27 Nov 2014 17:20:05 GMT

thank you for your input.

indeed this one is a workable solution.

But I wouldn't like to change the script; and since I need to apply this to many, many fields, I would like to have some sort of set analysis to easily use values from different fields.

Re: Expressions using values of different rows from same datatable

JonnyPoole — Thu, 27 Nov 2014 19:08:39 GMT

Here is something that works with this small data set

For the 1st calc , i used the total <PrimarySKU> to evaluate the subset of recrods for each unique Primary SKU (3 for PrimarySKU=A etc...) and spit out the record where SKU=PrimarySKU. If there are multiple records, just add the 'distinct' keyword after the "only(" part:

only( total <PrimarySKU> if(SKU=PrimarySKU,Availability))