Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
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
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