Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Highlighted
gopal5879
New Contributor III

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 Replies
mwoolf
Honored Contributor II

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

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
New Contributor III

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

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

Support
Support

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

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.