Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

[ASK] read next row data as a new column

Hi Qlik Masters,

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

BRANCHTARGETSTART_DATE
A501-JAN-2016
A701-FEB-2016
B301-MAR-2016
B701-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 :

BRANCHTARGETSTART_DATEEND_DATE
A501-JAN-201631-JAN-2016
A701-FEB-201616-NOV-2016
B301-MAR-201631-MAR-2016
B701-APR-201616-NOV-2016

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

Many thank you,

Best Regards

1 Solution

Accepted Solutions

Re: [ASK] read next row data as a new column

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;

View solution in original post

3 Replies
MVP
MVP

Re: [ASK] read next row data as a new column

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: [ASK] read next row data as a new column

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;

View solution in original post

Partner
Partner

Re: [ASK] read next row data as a new column

Dear Jonathan & Tamil,

it works!

Thank you for the best effort