topic Re: SCDType1 Issue in QlikView

SCDType1 Issue

Anonymous — Thu, 23 Mar 2017 17:53:33 GMT

Can somebody help me on this pls.

I have few order ID's where they keep changing their status on daily basis. Want to capture the last time updated record.

Suppose Order ID 14 in Day2 sheet of attached file updated 3 times on same day, my qlikview app refresh is also 3 times per day..so

I should capture only the last time it got updated for that day and should erase the other 2 records which updated that day.

Re: SCDType1 Issue

maxgro — Thu, 23 Mar 2017 18:21:41 GMT

Directory;

FOR Each d in 'Day1', 'Day2'

tmp:

LOAD

rowno() as ID,

'$(d)' as Day,

Order_ID,

Track_Location,

Delivered_Status,

Priority,

Warehouse_ID,

Ordered_Date,

Track_UpdatedDate

FROM SCD.xlsx

(ooxml, embedded labels, table is $(d));

NoConcatenate LOAD

Resident tmp

Where Order_ID <> Peek('Order_ID')

Order By Order_ID, Track_UpdatedDate desc, ID desc;

STORE T into T_$(d).qvd (qvd);

DROP Table T;

DROP Table tmp;

NEXT

LOAD * from T_Day?.qvd (qvd);

Re: SCDType1 Issue

Gysbert_Wassenaar — Thu, 23 Mar 2017 18:31:04 GMT

Something like this should do the trick:

Data:

LOAD

Order_ID,

Track_Location,

Delivered_Status,

Priority Warehouse_ID,

Ordered_Date,

Track_UpdatedDate,

Date(Floor(Track_UpdatedDate)) as Track_UpdatedDay

FROM

SCD.xlsx (ooxml, embedded labels, table is Day2)

;

RIGHT JOIN (Data)

LOAD

Order_ID,

Track_UpdatedDay,

Max(Track_UpdatedDate) as Track_UpdatedDate

RESIDENT

Data

GROUP BY

Order_ID,

Track_UpdatedDay

;

Re: SCDType1 Issue

Anonymous — Thu, 23 Mar 2017 18:47:49 GMT

Thanks Massimo and Gysbert for your prompt response...