left join in data model

[Aspiring_Developer]

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 

[edwin]

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
;

[edwin]

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

[Aspiring_Developer]

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 ???

[edwin]

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
;

[Aspiring_Developer]

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.