topic Re: SCD in QlikView

SCD

Thu, 18 Jun 2015 10:40:00 GMT

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

Re: SCD

jagan — Thu, 18 Jun 2015 10:51:39 GMT

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.

Re: SCD

swuehl — Thu, 18 Jun 2015 10:52:18 GMT

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;

Re: SCD

Thu, 18 Jun 2015 11:03:22 GMT

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.

Re: SCD

swuehl — Thu, 18 Jun 2015 12:38:30 GMT

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.