Table Join

[nigel987]

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

[felipedl]

Hi Nihal,

That was quite a challenge to do , but I think I got it.

Attached the QVW with the solution.

Felipe.

[#3]

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;