Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
Dear Jonathan & Tamil,
it works!
Thank you for the best effort