Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
bharatkishore
Creator III
Creator III

Peek or Previous

Hi All,

I have the below script:

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

NoConcatenate

T:

Load

      Equipment,

      System_Code,

      Calendar_Month_Code,

      Contract,

      Warranty,

      Installation,

      UnResolved,

      [T&M],

      [Part Group],

      Key_EMS,

      Install_Flag ,

      Removal_Flag ,

Technical_Start_Date,

($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))) AS Age

Resident Equipments_Details_final;

DROP Table Equipments_Details_final;

Now in the front end i load all the fields in a chart as below image:

T.PNG

Now since the data is huge i just loaded one key.. Now for this particular key we have an install flag.. So whenever we have install flag as 1 the next row in the age should be starting from 1 and then goes on..

For example in the attached qvw we have install flag for Calendar_Month_Code 2013-12 and Technical_Start_Date 2000-09, So till there we can see age as 160 and now from next row i.e. 2014-01 the age should starts from 1.


Can you please tell me how can i do it.


Attached qvw for reference


Thanks,

Bharat

25 Replies
bharatkishore
Creator III
Creator III
Author

Done that..

Kalmer
Partner - Creator
Partner - Creator

Hey,
So old logic still continues, a new Condition needs to be added.
PS: as you can see i changed the Previous(Flag) = 0. I made the condition vice-versa.

if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

     if(Previous(Install_Flag) = 0, rangesum(peek('Age'),1),

     if(Previous(Removal_Flag) = 0, rangesum(peek('Age'),1), 1)))  AS Age

bharatkishore
Creator III
Creator III
Author

Sorry now for both flags i am getting continuous numbers.. even the earlier condition is not working...

Kalmer
Partner - Creator
Partner - Creator

My mistake.

if(previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

if(Previous(Install_Flag) = 1 or Previous(Removal_Flag) = 1,1,rangesum(peek('Age'),1))) AS Age

That should do the trick

bharatkishore
Creator III
Creator III
Author

Let me try to explain more clearly:

please see the below image

T.PNG

The install flag what you have given is perfectly working fine.

Now one more condition is that if you see in the above image i have removal flag so there i need to get accumulated value till where there is 1. And from next all records  should become 0. There shouldn't be any more numbers.

Please let me know if something is not clear.

Thanks,

Bharat

Kalmer
Partner - Creator
Partner - Creator

Check my previous post, that should do it

bharatkishore
Creator III
Creator III
Author

I tried with that one.Please see the below image:

T.PNG

Here it 222 it is fine but from next i should get all 0 no more 1,2,3,.. and this should happen only for removal flag and not for install flag. Install flag is working perfectly fine..

Kalmer
Partner - Creator
Partner - Creator

so let me get this straight:
IF the Key_EMS changes, THEN we start from Month 1.
IF the Key_EMS is the same and the Install_Flag changes, THEN we start from Month 1,
IF the Key_EMS is the same and the Removal Flag comes in, THEN all values from next row in the same KEY_EMS = 0 (since it was removed) ?


I would do a preceeding load in here:

LOAD

*,

if(Previous(Removal_Flag) = 1, 0, if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1))) as Age2

;

LOAD

*,

if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

if(Previous(Install_Flag) = 1,1,rangesum(peek('Age'),1))) AS Age
;

bharatkishore
Creator III
Creator III
Author

HI K,

Thanks a lot..

I have given the below expression in one table it-self:

if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),

if(Previous(Install_Flag) = 1,rangesum(peek('Age'),1),

     if(Previous(Removal_Flag) = 1, 0, if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1))))) as Age2

And it is perfectly working fine. Is this correct right condition right or anything wrong..

Thank you so much...

Kalmer
Partner - Creator
Partner - Creator

i did not undestand this part "Is this correct right condition right or anything wrong.."
But as i understood it solved the problem.
Have good one!