Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewaf
Creator
Creator

Tabel: how to join ? I'm driving crazy!

Hello everyone!!

I have the following tables

Table1: on this table i would like to be able to add the information about Delivery Number and Delivery Date that are saved on another table (Table 2). 

qlikviewaf_0-1657553970093.png

The join from table 2, needs to be done using Item + Location + Lotn and then assign the most recent Delivery Number and Delivery Date  to the table 1 - so the rows highlighted in green should be reported in the table1. 

Complexity of this, is that if for an item-lot-location i have 2 (or maybe more records, as it is for item B-NewYork-CC12) i have to first assign the most recent value (1239038) and then the less recent value (1928397). It's like a chronological assignemnt.

Does anyone know if this can be achieved with qlikview?

Thanks!!!

qlikviewaf_1-1657554212983.png

 

 

Labels (3)
3 Replies
Mark_Little
Luminary
Luminary

Hi There,

I would first focus on the Second Table to get the values you want. Something like 

Load 

Item ,

Location ,

Lotn ,

MAX(Delivery Number) as Delivery Number

From Table 2

Group By Item , Location ,Lotn ;

Join

Load 

Item ,

Location ,

Lotn ,

Delivery Date

From Table 2;

This should give you the values shown in green and then can be joined to table 1.

qlikviewaf
Creator
Creator
Author

With your script i'm going to identify just the maximum value for "Delivery Number" ...but if you look item B - New York - CC12 i have to identify 2 maximum value for delivery number, not just one. How this can be done ? 

qlikviewaf
Creator
Creator
Author

any idea ?