[ASK] read next row data as a new column

[rendiyan]

Hi Qlik Masters,

i have a table that show target based on date, example below :

BRANCH	TARGET	START_DATE
A	5	01-JAN-2016
A	7	01-FEB-2016
B	3	01-MAR-2016
B	7	01-APR-2016

Based on above data, i don't have END_DATE and user expect me to set end_date from next start_date, if next start_date null then today.

Show the result will be like below :

BRANCH	TARGET	START_DATE	END_DATE
A	5	01-JAN-2016	31-JAN-2016
A	7	01-FEB-2016	16-NOV-2016
B	3	01-MAR-2016	31-MAR-2016
B	7	01-APR-2016	16-NOV-2016

Based on above example, is there any solution to do that in load script?

Many thank you,

Best Regards

[jonathandienst]

Load like this:

LOAD BRANCH,

  TARGET,

  START_DATE,

  If(Previous(BRANCH) = BRANCH,

  Previous(START_DATE),

  Today()

  ) as END_DATE

FROM ...

ORDER BY BRANCH, START_DATE DESC;

[tamilarasu · Accepted solution]

Hi Rendi,

Just a small correction in Jonathan's code.

Data:
LOAD BRANCH,
TARGET,
START_DATE
FROM
Source;

Final:
Load *,
If(BRANCH=Previous(BRANCH),Previous(START_DATE)-1,Today()) as END_DATE
Resident Data ORDER BY BRANCH, START_DATE DESC;

DROP Table Data;

[rendiyan]

Dear Jonathan & Tamil,

it works!

Thank you for the best effort