Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Gurus,
I am hoping someone can please help.
In the below example we have an order (order number = 9999) from a customer.
The customer is ordering 3 different Products with varying delivery dates.
The below table shows (in Blue) the scheduled quantity to be delivered and the quantity.
In Green it shows how many were delivered and the actual delivery date.
I am struggling to create the "Result" column.
I need a way of identifying the first actual delivery date and comparing it against the first scheduled delivery date, then the second actual delivery needs comparing to the second scheduled delivery... and so on.
To do this i would need a way to create individual lines as per below.
To start with I tried using iterNo() but the below script creates an IterNo() per delivery date (the lines in red should be 1 to 6 not, 1-2, 1-2, 1-2.
temp:
load * Inline [
Product , Qty , Del_Date
A , 2 , 01/30/2024
B , 1 , 01/31/2024
C , 2 , 02/01/2024
C , 2 , 02/02/2024
C , 2 , 02/03/2024
];
New:
Load
Product,
date(Del_Date,'YYYY-MM-DD') as Del_Date,
Qty,
IterNo() as Counter
Resident temp
While IterNo()<= Qty;
drop table temp;
Thanks in Advance
Ben
I managed to solve the problem with the following. Thanks
temp:
LOAD * INLINE [
Product, Qty, Del_Date
A, 2, 01/30/2024
B, 1, 01/31/2024
C, 2, 02/01/2024
C, 2, 02/02/2024
C, 2, 02/03/2024
];
TMP:
Load
Product,
date(Del_Date,'YYYY-MM-DD') as Del_Date,
Qty,
IterNo() AS Counter
Resident temp
While IterNo() <= Qty;
drop table temp;
TABLE:
NoConcatenate
LOAD *,
If(Product = Previous(Product),
RangeSum(Peek('Rank'), 1),1) as Rank
Resident TMP
Order By Product, Del_Date asc;
drop table TMP;
Hello, why not compare the dates directly? Otherwise, have you taken a look at this link? https://community.qlik.com/t5/QlikView-App-Dev/How-to-compare-two-date-values/td-p/932610
Thank you for the reply.
The difficulty I am having is the data source (SAP) has scheduled sale order line items
i.e. I can have 1 line for 2 off with the same contractual delivery date but I can delivery both items on different dates. There is no one to one relationship for me to compare dates.
I need to find a way to have the data showing as per the second screen shot, i.e. each line is a quantity of 1. Then I need to find a way to ensure that the first delivered date is compared to the first scheduled delivery date, and so on. Thanks again Ben
I see @ben_skerrett,
Please Try:
Hi Jorge,
Thank you for the comment.
Your script is so close. There is an issue with product C when I add the delivery date field. Each of the lines duplicate for each of the delivery dates.
The lines crossed out should not be visible. Thanks again
I managed to solve the problem with the following. Thanks
temp:
LOAD * INLINE [
Product, Qty, Del_Date
A, 2, 01/30/2024
B, 1, 01/31/2024
C, 2, 02/01/2024
C, 2, 02/02/2024
C, 2, 02/03/2024
];
TMP:
Load
Product,
date(Del_Date,'YYYY-MM-DD') as Del_Date,
Qty,
IterNo() AS Counter
Resident temp
While IterNo() <= Qty;
drop table temp;
TABLE:
NoConcatenate
LOAD *,
If(Product = Previous(Product),
RangeSum(Peek('Rank'), 1),1) as Rank
Resident TMP
Order By Product, Del_Date asc;
drop table TMP;