Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello folks, need little help, tried using peek and previous function but getting desired result.. I need Output for NEW_ORGANIZATION as below derived from ORGANIZATION until there is change in value of organization.
Can you someone give me hint or pseudo code please. Appreciate help
STATUS | REFERENCEDATE | WORKITEMID | ORGANIZATION | NEW_ORGANIATION |
Maintenance | 1/10/2018 | 2070400 | A1 | A1 |
Maintenance | 1/11/2018 | 2070400 | A1 | |
Maintenance | 1/12/2018 | 2070400 | A1 | |
Maintenance | 1/13/2018 | 2070400 | A2 | A2 |
Maintenance | 1/14/2018 | 2070400 | A2 | |
Maintenance | 1/15/2018 | 2070400 | A2 | |
Maintenance | 1/16/2018 | 2070400 | A2
|
DataTemp:
load * inline [
STATUS,REFERENCEDATE,WORKITEMID,ORGANIZATION
Maintenance,1/10/2018,2070400,A1
Maintenance,1/11/2018,2070400
Maintenance,1/12/2018,2070400
Maintenance,1/13/2018,2070400,A2
Maintenance,1/14/2018,2070400
Maintenance,1/15/2018,2070400
Maintenance,1/16/2018,2070400
];
Data:
NoConcatenate
load
*,
if(len(ORGANIZATION)=0,peek('NEW_ORGANIZATION'),ORGANIZATION) as NEW_ORGANIZATION
Resident DataTemp
order by STATUS,REFERENCEDATE,WORKITEMID;
drop table DataTemp;
Thanks a lot, Just wondering how the peek works? How does it know where the value is not null and null? Its kind of confusing ? can you please explain a bit more. Thanks a lot for your time
Gopal, I take it the post worked? If so, please be sure to use the Accept as Solution button to mark things and give credit to the poster. Regarding your follow-up question, here are a few links that should help:
https://community.qlik.com/t5/Qlik-Design-Blog/Peek-vs-Previous-When-to-Use-Each/ba-p/1475913
Regards,
Brett
DataTemp:
load * inline [
STATUS,REFERENCEDATE,WORKITEMID,ORGANIZATION
Maintenance,1/10/2018,2070400,A1
Maintenance,1/11/2018,2070400
Maintenance,1/12/2018,2070400
Maintenance,1/13/2018,2070400,A2
Maintenance,1/14/2018,2070400
Maintenance,1/15/2018,2070400
Maintenance,1/16/2018,2070400
];
Data:
NoConcatenate
load
*,
if(len(ORGANIZATION)=0,peek('NEW_ORGANIZATION'),ORGANIZATION) as NEW_ORGANIZATION
Resident DataTemp
order by STATUS,REFERENCEDATE,WORKITEMID;
drop table DataTemp;
Thanks a lot, Just wondering how the peek works? How does it know where the value is not null and null? Its kind of confusing ? can you please explain a bit more. Thanks a lot for your time
Gopal, I take it the post worked? If so, please be sure to use the Accept as Solution button to mark things and give credit to the poster. Regarding your follow-up question, here are a few links that should help:
https://community.qlik.com/t5/Qlik-Design-Blog/Peek-vs-Previous-When-to-Use-Each/ba-p/1475913
Regards,
Brett