Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

location per max date

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_NUMBERHEADER_IDLINE_IDBOOKED_DATESHIP_DATESHIP_ORG_CODESHIP_QTYRCV_DATERCV_ORG_CODERCV_QTY
303011817203920116099717/30/2017 9:06:55 AM8/19/2017 5:30:43 PMRW118/20/2017 4:16:37 PMKW11
303011817203920116099717/30/2017 9:06:55 AM9/12/2018 1:08:10 PMKW119/27/2018 8:30:47 PMDRT1
303011817203920116099717/30/2017 9:06:55 AM10/6/2018 2:30:53 PMDRT110/11/2018 9:55:35 AMDKT1

 

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_NUMBERHEADER_IDLINE_IDRow NoBOOKED_DATESHIP_DATESHIP_ORG_CODESHIP_QTYRCV_DATERCV_ORG_CODERCV_QTY
30301181720392011609971834727/30/2017 9:06:55 AM8/19/2017 5:30:43 PMRW118/20/2017 4:16:37 PMKW11
30301181720392011609971834737/30/2017 9:06:55 AM9/12/2018 1:08:10 PMKW119/27/2018 8:30:47 PMDRT1
30301181720392011609971834747/30/2017 9:06:55 AM10/6/2018 2:30:53 PMDRT110/11/2018 9:55:35 AMDKT1

 

anyone has any suggestions 🙂

 

 

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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

View solution in original post

2 Replies
Highlighted
Creator III
Creator III

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

View solution in original post

Highlighted
Creator
Creator

Thanks a ton Thomas, that worked