topic Re: Table Join in QlikView

Table Join

nigel987 — Tue, 20 Jun 2017 12:12:21 GMT

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

Re: Table Join

felipedl — Tue, 20 Jun 2017 14:58:50 GMT

Hi Nihal,

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

Attached the QVW with the solution.

Felipe.

Re: Table Join

Thu, 22 Jun 2017 22:52:14 GMT

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;