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

[qlikviewaf]

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). 

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

[Mark_Little]

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]

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]

any idea ?