Conditional Expression in Pivot Table

[akashaj094]

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 ?

[Saravanan_Desingh]

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, , 
];

[akashaj094]

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 ?

[Saravanan_Desingh · Accepted solution]

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;

[Saravanan_Desingh]

Output: