Product Status Change Timeline Data

[#1]

Hi Experts,

Here I have attached the excel with sample data. Left hand side table is my input and right hand side is the expected output.

Please guide me through the best approach.

Thanks in advance.

Pramod

[petter]

You can solve it the load script or in a table in the UI of Qlik Sense. What is your requirement?

[#3]

I want to get the data transformed in load script. How can I achieve this efficiently?

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD tAssetKey,

    tAssetStatus,

    FromDate,

    ToDate,

    OrderKey

FROM

[..\..\Downloads\AssetExcerise.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempTable:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('FromDateNew'), FromDate) as FromDateNew,

  ToDate

Resident Table

Order By tAssetKey, FromDate, ToDate;

TempTable1:

LOAD tAssetKey,

  tAssetStatus,

  FromDate,

  FromDateNew,

  ToDate,

  If(tAssetKey = Previous(tAssetKey) and tAssetStatus = Previous(tAssetStatus), Peek('ToDateNew'), ToDate) as ToDateNew

Resident TempTable

Order By tAssetKey, ToDate desc, FromDate desc;

FinalTable:

LOAD DISTINCT

  tAssetKey,

  tAssetStatus,

  FromDateNew as FromDate,

  Date(ToDateNew - 1) as ToDate

Resident TempTable1;

DROP Tables Table, TempTable, TempTable1;

[#5]

Thank you.