topic Re: Left join table on two columns qliksense in QlikView

Left join table on two columns qliksense

Aspiring_Developer — Mon, 22 Feb 2021 05:39:24 GMT

Hi all,

Request you to please help me with below.

I have below script:-

Data:
LOAD
tag_UID,
tag_Material,
tag_Mat_Description,
tag_SkidID,
tag_LogicalSkidID,
tag_SkidPosition,
tag_Version,
tag_Color,
tag_Changer,
tag_ChangeDate,
tag_TrolleyID,
tag_Undetected,
tag_Mold,
tag_Cavity,
tag_ProdDate,
tag_UndetecCount,
tag_InternalID,
tag_IMPCCycleNumber,
tag_IMPCCompliance,
tag_IMPCCreatedAt,
tag_QualityStatus
FROM [lib://Shopfloor/GREER/TAG/P_Tag_Data.qvd]
(qvd);

T1:
Load
Distinct(tag_UID) as t.tag_UID

Resident Data
where len(trim(tag_Changer)) >0 and not wildmatch(tag_Changer, '*Undetected_FeragGate1*', '*Undetected_FeragGate3*',
'*Undetected_FeragGate4*','*Undetected_FeragGate5.2*','*Undetected_FeragGate7*','*Undetected_FeragGate8*',
'*Undetected_FeragGate6*','*Undetected_FeragGate2*') ;

//----------------------------------------------------------//
left join(T1)

T2:
Load tag_UID,
Max(tag_ChangeDate) as Max_Datetime_mold
resident Data
where len(trim(tag_Changer)) >0 and not wildmatch(tag_Changer, '*Undetected_FeragGate1*', '*Undetected_FeragGate3*',
'*Undetected_FeragGate4*','*Undetected_FeragGate5.2*','*Undetected_FeragGate7*','*Undetected_FeragGate8*',
'*Undetected_FeragGate6*','*Undetected_FeragGate2*') and
tag_Color = 'NULL'

group by tag_UID
;

//--------------------------------------------------------------
left join (T1)

T3:
Load tag_UID,
Max(tag_ChangeDate) as Max_Datetime_paint
resident Data
where len(trim(tag_Changer)) >0 and not wildmatch(tag_Changer, '*Undetected_FeragGate1*', '*Undetected_FeragGate3*',
'*Undetected_FeragGate4*','*Undetected_FeragGate5.2*','*Undetected_FeragGate7*','*Undetected_FeragGate8*',
'*Undetected_FeragGate6*','*Undetected_FeragGate2*') and
tag_Color <> 'NULL'
group by tag_UID
;

=====================================

Now, i have two tables 'Data' and 'T1'

My requirement is :-

T1 - left join Raw data (Tag uid, Max_Datetime_paint)--new table

Newtable- left join Raw Data ((tag uid, Max_Datetime_mold))

==================

So, in the final table, all the data should be available

Can anyone please help ?

Re: Left join table on two columns qliksense

MayilVahanan — Mon, 22 Feb 2021 08:45:02 GMT

HI @Aspiring_Developer

Hope Tag_UID is primary key. In that case, you can simply join the raw data & T1 like below

after ur script, add like below

Left join(Data) Load * resident T1;

Drop table T1;

Re: Left join table on two columns qliksense

Aspiring_Developer — Mon, 22 Feb 2021 09:01:30 GMT

Hi @MayilVahanan

I have to join the tables on two fields (Tag_UID & Max_Datetime_paint)

And while applying left join T1 should come first in the table

It should be like below :-

T1(left join) Data on keys fields (Tag_UID & Max_Datetime_paint)-- new table created here

Then

Newtable (left join) with T1 on key fields (Tag_UID & Max_Datetime_mold)

Can you please help me ? I am stuck

Re: Left join table on two columns qliksense

MayilVahanan — Mon, 22 Feb 2021 09:42:21 GMT

Hi @Aspiring_Developer

In that case, create a key field in "tag_UID" &tag_ChangeDate in Data and tag_UID & Max_Datetime_paint , then join based on Key field instead of "Tag_UID" alone.

Hope it helps

Re: Left join table on two columns qliksense

Aspiring_Developer — Mon, 22 Feb 2021 10:37:22 GMT

So , should be like below:-

TableA:

Load tag_UID & Max_Datetime_paint

Resident T1

Left join (Data)

Data:

Load

Tga_UID & tag_ChangeDate as key

Resident Data