Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Kindly help me resolve the following query using the SCD solution
Well, I have the following Inline table
LOAD * INLINE
[
Product, ProdName, Price, Updated
123, Watch, 150$, 1/1/2015
134, Fruit, 30$, 1/1/2015
145, Ring, 3000$, 3/1/2015
123, Watch, 125$, 5/1/2015
123, Watch, 119$, 5/5/2015
134, Fruit , 50$, 5/5/2015
145, Ring, 2500$, 5/12/2015
234, Pen, 12$, 5/17/2015
312, Jeans, 50$, 6/9/2015
];
Using the above input table, I need to get the below reults
Product | ProdName | Price | Updated | From | To Date |
123 | Watch | 150$ | 1/1/2015 | 1/1/2015 | 4/30/2015 |
123 | Watch | 125$ | 5/1/2015 | 5/1/2015 | 5/42015 |
123 | Watch | 119$ | 5/5/2015 | 5/5/2015 | 6/18/2015 |
Help me implementing the same.
Thanks in advance
Hi,
Try this script
Temp:
LOAD
Product, ProdName, Price, Date(Updated) AS Updated INLINE
[
Product, ProdName, Price, Updated
123, Watch, 150$, 1/1/2015
134, Fruit, 30$, 1/1/2015
145, Ring, 3000$, 3/1/2015
123, Watch, 125$, 5/1/2015
123, Watch, 119$, 5/5/2015
134, Fruit , 50$, 5/5/2015
145, Ring, 2500$, 5/12/2015
234, Pen, 12$, 5/17/2015
312, Jeans, 50$, 6/9/2015
];
Data:
LOAD
*,
Updated AS From,
If(Peek(Product) <> Product, Today(), Date(Peek(Updated) - 1)) AS To
RESIDENT Temp
ORDER BY Product, ProdName, Updated Desc;
DROP TABLE Temp;
Regards,
Jagan.
Try like this:
Set DateFormat = 'M/D/YYYY';
INPUT:
LOAD * INLINE
[
Product, ProdName, Price, Updated
123, Watch, 150$, 1/1/2015
134, Fruit, 30$, 1/1/2015
145, Ring, 3000$, 3/1/2015
123, Watch, 125$, 5/1/2015
123, Watch, 119$, 5/5/2015
134, Fruit , 50$, 5/5/2015
145, Ring, 2500$, 5/12/2015
234, Pen, 12$, 5/17/2015
312, Jeans, 50$, 6/9/2015
];
RESULT:
LOAD Product, ProdName, Price, Updated,
Updated as From_Date,
if(peek(Product) = Product, Date(peek(Updated)-1), Date(today())) as To_Date
Resident INPUT
ORDER BY Product, Updated desc;
DROP TABLE INPUT;
Hi Swuehi and Jagan
Thanks for your kind help.
Both of your solution works.
But can anyone explain me what exactly is been done in the query above to get the required result, as I can add this to my learning being a new person to QlikView.
The RESULT table is loading the INPUT table in a certain order, first sorted by Product, then by Updated date descending. Now the table comes in with Product 123 first, newest update record first.
The peek() function is looking at the resulting table, so for the first record coming in, there will be no resulting record present, so the comparison will be false and To_Date will be today. Same will happen for any change in Product.
For all other records, QV looks of the Updated value of the resulting record before, you can think of 'one line up'.
Subtracting 1 gives you the To_Date for all these records.
It's best if you try to 'feed' records in the LOAD script one after the other and try to understand how QV is using peek() to access the records already processed before.