Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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
hi
try something like this
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,
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;
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
stalwar1 Sunny Bhai.. Can you please help..
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!
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
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
Wow.. this worked.. Thank you so much K.
But can you please tell me what will happen with the logic you have written.
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
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