Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have this table
UserID | Typ | Zeitraum | Anzahl |
15 | P1 | 30 | 0 |
15 | P2 | 30 | 1 |
15 | P3 | 30 | 0 |
and I would like to create this one (via script):
UserID | Duration2 | HasP1 | HasP2 | HasP3 |
15 | 30 | No | Yes | No |
15 | 60 | Yes | No | Yes |
so that both tables join via the UserId field.
Can someone help me please, I'm stuck ;/
KR, Nigel
Hi Nihal,
That was quite a challenge to do , but I think I got it.
Attached the QVW with the solution.
Felipe.
Hi Nihal,
I tried your problem with the crosstab function - this only works if your source table contains already all valid combinations (Anzahl is 0 for non existing records). It was not not sure by the given example if i can asume that. Please refer to my draft and you can see how it looks like. I enlarged the source data a bit (2 Users and one typ "P7" which is only for user 16).
alex
//SourceData
UserData:
Load * inline
[
UserID,Typ,Zeitraum,Anzahl
15,P1,30,0
15,P2,30,1
15,P3,30,0
16,P1,30,2
16,P3,30,3
15,P1,60,23
15,P2,60,100
16,P7,60,2
16,P3,60,30
];
//Pivot the Data - 2 ... keep 1st 2 columns, make column names with the value of column "Typ" the Column value is "Anzahl"
UserDataPivot:
CROSSTABLE (2, Typ, Anzahl) LOAD UserID, Zeitraum, Typ, if(Anzahl>0,'Yes', 'No') As Anzahl resident UserData;
drop Table UserData;