location per max date

[kaldubai]

Hey all,

i hope you all are enjoying this new look of the site.

so i have a table where orders and lines are stored just like below and it shows the movements of  the items from one location to another.

ORDER_NUMBER	HEADER_ID	LINE_ID	BOOKED_DATE	SHIP_DATE	SHIP_ORG_CODE	SHIP_QTY	RCV_DATE	RCV_ORG_CODE	RCV_QTY
303011817	2039201	1609971	7/30/2017 9:06:55 AM	8/19/2017 5:30:43 PM	RW1	1	8/20/2017 4:16:37 PM	KW1	1
303011817	2039201	1609971	7/30/2017 9:06:55 AM	9/12/2018 1:08:10 PM	KW1	1	9/27/2018 8:30:47 PM	DRT	1
303011817	2039201	1609971	7/30/2017 9:06:55 AM	10/6/2018 2:30:53 PM	DRT	1	10/11/2018 9:55:35 AM	DKT	1

now the requirements is to allow the user to pass the date and based on that it should show where is the last location of the order 

for example if the user passes 10/31/2018 it should show DKT as RCV_ORG 

and if the user passes 9/30/2018 it should show DRT as RCV_ORG 

now what i have been able to think of so far is to use order by  and then give a row number to each line,like below table , based on ORDER_NUMBER,HEADER_ID,LINE_ID,SHIP_DATE  in order to pick the max line but unfortunately im  not able to do  so. 

ORDER_NUMBER	HEADER_ID	LINE_ID	Row No	BOOKED_DATE	SHIP_DATE	SHIP_ORG_CODE	SHIP_QTY	RCV_DATE	RCV_ORG_CODE	RCV_QTY
303011817	2039201	1609971	83472	7/30/2017 9:06:55 AM	8/19/2017 5:30:43 PM	RW1	1	8/20/2017 4:16:37 PM	KW1	1
303011817	2039201	1609971	83473	7/30/2017 9:06:55 AM	9/12/2018 1:08:10 PM	KW1	1	9/27/2018 8:30:47 PM	DRT	1
303011817	2039201	1609971	83474	7/30/2017 9:06:55 AM	10/6/2018 2:30:53 PM	DRT	1	10/11/2018 9:55:35 AM	DKT	1

anyone has any suggestions 🙂

[thomaslg_wq · Accepted solution]

Here you go, hope this helps !

// Your data
Raw_Data:
LOAD
ORDER_NUMBER&'|'&HEADER_ID&'|'&LINE_ID as Key,
ORDER_NUMBER,
HEADER_ID,
LINE_ID,
BOOKED_DATE,
SHIP_DATE,
SHIP_ORG_CODE,
SHIP_QTY,
RCV_DATE,
floor(RCV_DATE) as From_Ref_Date,
RCV_ORG_CODE,
RCV_QTY
FROM [lib://Desktop/Classeur2.xlsx]
(ooxml, embedded labels, table is Feuil1);

// Create ranges of dates (from / to) + key
Data:
LOAD *,
From_Ref_Date&'|'&To_Ref_Date as Key_Date_Data;
Load
*,
if(recno()=1 or Key<>peek('Key',-1), floor(now()), peek('From_Ref_Date',-1)-1) as To_Ref_Date
Resident Raw_Data
Order by Key, From_Ref_Date desc;

Drop table Raw_Data;

// Create some dates
Calendar:
LOAD
TempDate AS Ref_Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month;
LOAD
date(mindate + IterNo()) AS TempDate
WHILE mindate + IterNo() <= floor(now());
LOAD
min(FieldValue('From_Ref_Date', recno()))-1 as mindate
AUTOGENERATE FieldValueCount('From_Ref_Date');

// Create a bridge between dates and fact
Tmp_Bridge:
intervalmatch(Ref_Date)
LOAD
From_Ref_Date,
To_Ref_Date
Resident Data;

Bridge:
LOAD
Ref_Date,
From_Ref_Date&'|'&To_Ref_Date as Key_Date_Data
Resident Tmp_Bridge;

Drop table Tmp_Bridge;

Drop fields From_Ref_Date, To_Ref_Date;

Then with this, which is a start, you can select a date from calendar and see the allocation on this date

Regards,

Thomas

[kaldubai]

Thanks a ton Thomas, that worked