topic left join in data model in QlikView

left join in data model

Aspiring_Developer — Tue, 23 Feb 2021 09:48:22 GMT

Hello All,

I have below data model:-

Below is the 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
[C:\Users\admin.vbhardwaj\Desktop\P_Tag_Data.qvd]
(qvd) where trim(len(tag_Changer))>0 and
not wildmatch(tag_Changer, 'Undetected_FeragGate1', 'Undetected_FeragGate2','Undetected_FeragGate3', 'Undetected_FeragGate4', 'Undetected_FeragGate5.2' ,'Undetected_FeragGate6', 'Undetected_FeragGate7'
,'Undetected_FeragGate8') ;

//===To create distinct tag_uid===//
T1:
Load distinct (tag_UID)
Resident Data;

//=====To create tag mold==//

left join (T1)

T2:
Load
tag_UID,
Max(tag_ChangeDate) as Max_Datetime_mold
resident Data
where tag_Color = 'NULL'
group by tag_UID
;

//===To create tag paint--//

left join (T1)

T3:
Load tag_UID,
Max(tag_ChangeDate) as Max_Datetime_paint
resident Data
where tag_Color <> 'NULL'

group by tag_UID ;

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

I want to achieve below in qlik

Table T1 (left join) with Data on Tga_uid and Max_Datetime_mold === new table

New table (left join) with T1 on Tga_uid and Max_Datetime_paint

------------------------------------------------

Also when i am joining the table i also need to achieve below:-

Table T1 left join Table Data

In table data , there should be field like below:-

tag_TrolleyID as tag_TrolleyID_paint,

And when performing second join, the data table should have the feilds

tag_TrolleyID as tag_TrolleyID_mold.

In short, in the final tabl i should have the above two differen fields in the single table.

I have written another post with some additional information as i am not able to find any solution.

Request you to please help !!

Thank You

@sunny_talwar @Kushal_Chawda @swuehl

Re: left join in data model

edwin — Tue, 23 Feb 2021 13:11:36 GMT

i would tweak this just a little:

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, if(tag_Color = 'NULL', tag_TrolleyID ) as tag_TrolleyID_mold, if(tag_Color <> 'NULL', tag_TrolleyID ) as tag_TrolleyID_paint, tag_Undetected, tag_Mold, tag_Cavity, tag_ProdDate, tag_UndetecCount, tag_InternalID, tag_IMPCCycleNumber, tag_IMPCCompliance, tag_IMPCCreatedAt, tag_QualityStatus, if(tag_Color = 'NULL', tag_ChangeDate) as Datetime_mold, if(tag_Color <> 'NULL', tag_ChangeDate) as Datetime_paint FROM [C:\Users\admin.vbhardwaj\Desktop\P_Tag_Data.qvd] (qvd) where trim(len(tag_Changer))>0 and not wildmatch(tag_Changer, 'Undetected_FeragGate1', 'Undetected_FeragGate2','Undetected_FeragGate3', 'Undetected_FeragGate4', 'Undetected_FeragGate5.2' ,'Undetected_FeragGate6', 'Undetected_FeragGate7' ,'Undetected_FeragGate8') ; //===To create distinct tag_uid===// T1: Load tag_UID, Max(Datetime_mold) as Max_Datetime_mold, Max(Datetime_paint) as Max_Datetime_paint resident Data group by tag_UID ;

Re: left join in data model

edwin — Tue, 23 Feb 2021 13:10:24 GMT

you actually dont need to join the tables again as the two tables are already associated by tag_UID. there must be a reason why you want it joined maybe you can explain that

Re: left join in data model

Aspiring_Developer — Tue, 23 Feb 2021 14:10:34 GMT

Hi @edwin

Many thanks for sharing the resiolution as i was struggling to find the soliution.

The reason we need join is to create two tables , because

1) We need to take distinct tag_UID

2) Using the above distinct tag_UID , we have to find max_Datetime_mold where tag_colors =NULL

and Max_Datetime_paint where tag_color <> 'NULL'

The above two operations should be perfomed with distinct tag_uid and then

for final table we join Data table (having all ID's ) and T1 table (having distinct Itag_uid's)

End result should be single table having all fields (including Max_Datetime_mold and Max_Datetime_paint) whic you have created in T1 table.

Can you please help ???

Re: left join in data model

edwin — Tue, 23 Feb 2021 14:39:59 GMT

if you create a table aggregated /grouped by tag_IUD you will result in a distinct of each.

im saying you dont need to join but if you want, you can inner join again to get a single table.

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, if(tag_Color = 'NULL', tag_TrolleyID ) as tag_TrolleyID_mold, if(tag_Color <> 'NULL', tag_TrolleyID ) as tag_TrolleyID_paint, tag_Undetected, tag_Mold, tag_Cavity, tag_ProdDate, tag_UndetecCount, tag_InternalID, tag_IMPCCycleNumber, tag_IMPCCompliance, tag_IMPCCreatedAt, tag_QualityStatus, if(tag_Color = 'NULL', tag_ChangeDate) as Datetime_mold, if(tag_Color <> 'NULL', tag_ChangeDate) as Datetime_paint FROM [C:\Users\admin.vbhardwaj\Desktop\P_Tag_Data.qvd] (qvd) where trim(len(tag_Changer))>0 and not wildmatch(tag_Changer, 'Undetected_FeragGate1', 'Undetected_FeragGate2','Undetected_FeragGate3', 'Undetected_FeragGate4', 'Undetected_FeragGate5.2' ,'Undetected_FeragGate6', 'Undetected_FeragGate7' ,'Undetected_FeragGate8') ; inner join (Data) Load tag_UID, Max(Datetime_mold) as Max_Datetime_mold, Max(Datetime_paint) as Max_Datetime_paint resident Data group by tag_UID ;

Re: left join in data model

Aspiring_Developer — Wed, 24 Feb 2021 09:47:27 GMT

Hi @edwin

I wish to achieve below :-

For the newly created table (in the application it will be another table than the original tag_data table) we should have only one line per part.

Also the different join should be done between the tag_uid and the tag_changedate.