Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SCD

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

ProductProdNamePriceUpdatedFromTo Date
123Watch150$1/1/20151/1/20154/30/2015
123Watch125$5/1/20155/1/2015     5/42015
123Watch119$5/5/20155/5/20156/18/2015

Help me implementing the same.

Thanks in advance

4 Replies
jagan
Luminary Alumni
Luminary Alumni

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.

swuehl
MVP
MVP

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;

Not applicable
Author

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.

swuehl
MVP
MVP

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.