Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Aspiring_Developer
Creator II
Creator II

left join in data model

Hello All,

I have below data model:-

Aspiring_Developer_0-1614065712110.png

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

Aspiring_Developer_1-1614066828731.png

 

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 @Kush @swuehl 

 

 

5 Replies
edwin
Specialist III
Specialist III

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
Specialist III
Specialist III

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
Creator II
Creator II
Author

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
Specialist III
Specialist III

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
Creator II
Creator II
Author

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.