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
Hi K,
Just a question,
Need to pass one more else condition in the below formula is
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
Now where ever i have install and removal flag as 1 then next row should be all 0.
For example please see the below image:
Here till 75 is fine but from next row it should be all 0.
Can you please help.
Thanks,
Bharat
Hi!
You just need to create all the results for all combinations possible. (It should be pretty easy from here, since you have most of the script done).
Try this:
if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),
if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 1, 0,
if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 0, rangesum(peek('Age'),1),
if(Previous(Install_Flag) = 0 and Previous(Removal_Flag) = 1, 0,
if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1)))))) as Age2
K
Thanks a lot K. You are simplly amazing.. Thank you so much..
Hi K,
One final question on this topic hopefully....
Please see the below image
Here if you see in KEY_EMS there we have some nulls and if you see in the Age2 column there the subtraction is not happening correct(between technical_start_Date - Calendar_Month_Code) and the months are showing in-correct.
Can you please check and let me know what i need to do.
Thanks,
Bharat
1) Make sure your order is in asc in script:
YourTable:
Load
*
FROM (Connection)
Where clause
Order by KEY_EMS, Calendar_Month_Code asc;
2) I have a feeling that there could be some issues with Null() stringed values on KEY_EMS. So i would do the following and on your load statement to make it selectable:
YourTable:
Load
Equipment,
System_Code,
Calendar_Month_Code,
Contract,
Warranty,
Installation,
UnResolved,
[T&M],
[Part Group],
if(len(Key_EMS)< 1, "NO EMS KEY", Key_EMS) as Key_EMS
Install_Flag ,
Removal_Flag ,
Technical_Start_Date
FROM (Connection)
Where clause
Order by KEY_EMS, Calendar_Month_Code asc;
3) Now i know that i don't have anymore empty strings, then the previous logic should work in here aswell with the previous script
YourTable:
Load
*,
if(Previous(Key_EMS) <> Key_EMS,($(MonthDiff(Technical_Start_Date, Calendar_Month_Code))),
if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 1, 0,
if(Previous(Install_Flag) = 1 and Previous(Removal_Flag) = 0, rangesum(peek('Age'),1),
if(Previous(Install_Flag) = 0 and Previous(Removal_Flag) = 1, 0,
if(Previous(Peek('Age2')) = 0, 0, Rangesum(Peek('Age2'),1)))))) as Age2
;
Load
Equipment,
System_Code,
Calendar_Month_Code,
Contract,
Warranty,
Installation,
UnResolved,
[T&M],
[Part Group],
if(len(Key_EMS)< 1, "NO EMS KEY", Key_EMS) as Key_EMS
Install_Flag ,
Removal_Flag ,
Technical_Start_Date
FROM (Connection)
Where clause
Order by KEY_EMS, Calendar_Month_Code asc;
Since i dont know what exactly is written in your 'Age' calculation, you need to add it aswell to the load script. It's not added right now.
Hi K,
Thanks a lot for your help.
But i am not sure where to add the code.
Can you please help me where to add in the attached file.