topic Re: Conditional Expression in Pivot Table in QlikView

Conditional Expression in Pivot Table

akashaj094 — Sat, 16 Nov 2024 00:23:05 GMT

Hi Everyone

Disclaimer: The data or any variable represented in this post is dummy data and is not valid.

I'm working on this problem of Pivot Table. I have a cumulative pivot table. I want to create an incremental pivot table

Table 1:

Now from this table i want to create a table that looks like this

Here Column X remains the same. (1,Y) = (1,Y) from first - (1,X) from first and so on. The '-' remains the same wherever it's found. Can someone tell me how to do this ?

I wrote an if else in the expression, but it doesn't take care of the '-' and gives me (1,P) = -56

I'm getting an output as:

ABC	X	Y	Z	P
1	23	45	56	-56
2	34	23	-23	-
3	12	-12	-	-
4	11	-11	-	-

Any help ?

Re: Conditional Expression in Pivot Table

Saravanan_Desingh — Thu, 02 Jul 2020 13:24:02 GMT

One solution is.

tab1: LOAD ABC, X, Y-X As Y, Z-Y As Z, P-Z As P; LOAD * INLINE [ ABC, X, Y, Z, P 1, 23, 45, 56, 2, 34, 23, , 3, 12, , , 4, 11, , ];

Re: Conditional Expression in Pivot Table

akashaj094 — Thu, 02 Jul 2020 13:34:39 GMT

Thanks for the solution !

The solution looks good. But, i have two dimensions. Dim 2 has around 38 values (X,Y,Z and so on) for which the process stated by you would take a lot of time.

Is there any easy way to do this ?

Re: Conditional Expression in Pivot Table

Saravanan_Desingh — Thu, 02 Jul 2020 17:14:29 GMT

Are you looking something like this?

tab1: LOAD * INLINE [ ABC, X, Y, Z, P, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15 1, 23, 45, 56, , 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 2, 34, 23, , , , , , , , , , , , , , , , , 3, 12, , , , , , , , , , , , , , , , , , 4, 11, , , , , , , , , , , , , , , , , , ]; tabX: CrossTable(Dim2, Value) LOAD * Resident tab1; tab2: NoConcatenate LOAD *, If(Peek(ABC)=ABC, Value-Peek(Value), Value) As Value2 Resident tabX; Gen: Generic LOAD ABC, Dim2, Value2 Resident tab2; Drop Table tab1, tabX, tab2;

Re: Conditional Expression in Pivot Table

Saravanan_Desingh — Thu, 02 Jul 2020 17:15:11 GMT

Output: