topic Re: Peek or Previous in QlikView

Peek or Previous

bharatkishore — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have the below script:

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

NoConcatenate

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:

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

Re: Peek or Previous

lironbaram — Fri, 07 Sep 2018 09:44:33 GMT

try something like this

Load

Equipment,

System_Code,

Calendar_Month_Code,

Contract,

Warranty,

Installation,

UnResolved,

[T&M],

[Part Group],

Key_EMS,

Install_Flag ,

Removal_Flag ,

Technical_Start_Date,

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

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

Resident Equipments_Details_final

order by Key_EMS,Calendar_Month_Code;

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 10:15:47 GMT

Hi Liron,

Thanks a lot for your reply.

Only thing is as of now from where i have install flag there it is showing as 1 but 1 should come from the next record.

Attached image for more reference:

Here in the image we have install flag at 2016-11 and 1998-06 in technical start date. Here the age is starting from 1 , but instead of this till where i have install flag as 1 till there it should me 222 and the next record i.e. 2016-12 and technical start date 1998-06 from there it should start from 1.

Please let me know if you need anything more.

Thanks,

Bharat

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 11:14:58 GMT

stalwar1‌ Sunny Bhai.. Can you please help..

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 12:22:01 GMT

Try this:

Piece of old code:

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

Replace it with:

if(Previous(Install_Flag) <> InstallFlag, 1, Rangesum(Peek('Age'),1))) as Age

Good luck!

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 12:29:39 GMT

Thank you Kalmer.

But the problem is please see the below image:

Here where install flag is there till there i need to get the aggregated one. So here in this case i am getting 1 but there is should get 222 then from next record i should get 1 which now i am getting.

Just if we can get that 222 value there then it will done.

Thanks,

Bharat

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 12:39:24 GMT

Strange, the last piece of script should have fixed it.

if(Previous(Install_Flag) <> InstallFlag, 1, Rangesum(Peek('Age'),1))) as Age - As in, if last row was different from current row we start the Age.
Hmm try then (same row):
if(Previous(InstallFlag) = 1,1,rangesum(peek('Age'),1))) AS Age

NP!
K

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 12:42:43 GMT

Wow.. this worked.. Thank you so much K.

But can you please tell me what will happen with the logic you have written.

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 12:47:06 GMT

If you mean in this Thread (Re: Need to calculate Age), then it operates pretty much the same way.
But i guess we miss-spoke with the Install/Removal Flags.

Could you please put a link TO THIS POST in the post mentioned above in THIS comment. Also putting a "Correct" wouldn't harm me

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 12:47:52 GMT

Can i ask you one question please,

Now in this code i need to pass one more else condtion

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

like where removal flag is 1 till there i need to get aggregated value and from the next row it should be 0 for all the remaining rows.

Can you please help me how to do it.

Please don't mind.

Thanks,

Bharat

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 12:52:19 GMT

Done that..

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 12:54:56 GMT

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

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 13:04:41 GMT

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

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 13:11:20 GMT

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

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 13:14:12 GMT

Let me try to explain more clearly:

please see the below image

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

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 13:18:39 GMT

Check my previous post, that should do it

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 13:22:23 GMT

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

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..

Re: Peek or Previous

Kalmer — Fri, 07 Sep 2018 13:39:51 GMT

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
;

Re: Peek or Previous

bharatkishore — Fri, 07 Sep 2018 14:17:54 GMT

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...

Re: Peek or Previous

Kalmer — Sat, 08 Sep 2018 08:02:38 GMT

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!