Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopal5879
Creator
Creator

Help - find the last valid occurrence as New derived filed, last valid may not be immediate prior

 

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

 

STATUSREFERENCEDATEWORKITEMIDORGANIZATIONNEW_ORGANIATION
Maintenance1/10/20182070400A1A1
Maintenance1/11/20182070400 A1
Maintenance1/12/20182070400 A1
Maintenance1/13/20182070400A2A2
Maintenance1/14/20182070400 A2
Maintenance1/15/20182070400 A2
Maintenance1/16/20182070400 

A2

 

3 Solutions

Accepted Solutions
m_woolf
Master II
Master II

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;

View solution in original post

gopal5879
Creator
Creator
Author

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

View solution in original post

Brett_Bleess
Former Employee
Former Employee

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

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

3 Replies
m_woolf
Master II
Master II

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;

gopal5879
Creator
Creator
Author

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

Brett_Bleess
Former Employee
Former Employee

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

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Inter...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.