Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI All,
I have the following columns
Equipment,
System_Code,
Calendar_Month_Code,
Contract,
Warranty,
Installation,
UnResolved,
[T&M],
[Part Group],
Key_EMS,
Install_Flag ,
Removal_Flag ,
Technical_Start_Date
Attaching image for reference:
Here i have key column and install flag and removal flag.
What i need to achieve is :
First i need to create a field called Age then for any particular key whenever the install flag and removal flag are 0 i need to do is Technical_Start_Date-Calendar_Month_Code then whatever the first value i get then from the next it should be increment of second row.
Say for 2012-01 we have got first value as 80 then from second row i need to increment as 81,82.....till the last date.
Can you please tell me how to achive it.
Thanks,
Bharat
Age was easy.
Change of KEY_EMS based on the previous value was a bit more tricky. Hope i got it right. Here are the rules as i understood them:
1) If InstallFlag=0 AND RemovalFlag = 0 THEN Increase the value KEY_EMS based on the last value
2) The last value resets, if KEY_EMS is changed completeley from the old tabel. As in ProductID A, B, C and so on.
CODE:
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
Tabel1:
Load
Calendar_Month_Code,
Technical_Start_Date,
Part_Group,
Equipment,
Removal_Flag,
Install_Flag,
Key_EMS as OLD_Key_EMS,
if(Removal_Flag = 0 and Install_Flag = 0 and Key_EMS = peek(OLD_Key_EMS), if(IsNull(Previous(Key_EMS)), Key_EMS, Peek(Key_EMS)+1),Key_EMS) as Key_EMS,
$(MonthDiff(TechnicalDate, CalendarDate)) AS Age
;
Load
*,
MakeDate(Subfield(Calendar_Month_Code, '-', 1), Subfield(Calendar_Month_Code, '-', -1)) as CalendarDate,
MakeDate(Subfield(Technical_Start_Date, '-', 1), Subfield(Technical_Start_Date, '-', -1)) as TechnicalDate
;
Load * Inline [
Calendar_Month_Code, Technical_Start_Date, Key_EMS, Part_Group, Equipment, Removal_Flag, Install_Flag
2012-01, 2011-12, 10105, MCR 0508, 10105, 0, 0
2012-02, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-03, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-04, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-01, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-02, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-03, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-04, 2011-09, 10106, MCR 0509, 10110, 0, 0
];
Hope i got it right, let me know.
K
Quite don't undestand what you want to achieve, it does not sound clear enough to help fully out .
I hope the value of "80" is in a field. Right now you have sum(1). If it's in a field, it would be easier.
YourTable:
Load
*,
Previous(SumValue)+SumValue as SumValue2
;
Load
*,
SumValue,
Interval(MakeDate(Subfield(Calendar_Month_Code, '-', 1), Subfield(Calendar_Month_Code, '-', -1)) - MakeDate(Subfield(Technical_Start_Date, '-', 1), Subfield(Technical_Start_Date, '-', -1)) as Age
Where Removal_Flag = 0 and Install_Flag = 0;
//Order by Calendar_Month_Code desc;
Hi Kalmer,
Thanks for reply. Kindly please find the explanation below hopefully will be better. Kindly ignore the sum expression
Firstly i have two dates i.e. Calendar_Month_Code and Technical_Start_Date .. Now i want to do Technical_Start_Date - Calendar_Month_Code and the result output should be in months and this should be derived in a new field called Age.
Secondly say in the age column you got the first value as 10 based on any key you selected in KEY_EMS. Then based on first value the next rows should be increment of 1 for wherever install flag and return flag is 0.
Kindly let me know if something is not clear.
Thanks,
Bharat
Age was easy.
Change of KEY_EMS based on the previous value was a bit more tricky. Hope i got it right. Here are the rules as i understood them:
1) If InstallFlag=0 AND RemovalFlag = 0 THEN Increase the value KEY_EMS based on the last value
2) The last value resets, if KEY_EMS is changed completeley from the old tabel. As in ProductID A, B, C and so on.
CODE:
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
Tabel1:
Load
Calendar_Month_Code,
Technical_Start_Date,
Part_Group,
Equipment,
Removal_Flag,
Install_Flag,
Key_EMS as OLD_Key_EMS,
if(Removal_Flag = 0 and Install_Flag = 0 and Key_EMS = peek(OLD_Key_EMS), if(IsNull(Previous(Key_EMS)), Key_EMS, Peek(Key_EMS)+1),Key_EMS) as Key_EMS,
$(MonthDiff(TechnicalDate, CalendarDate)) AS Age
;
Load
*,
MakeDate(Subfield(Calendar_Month_Code, '-', 1), Subfield(Calendar_Month_Code, '-', -1)) as CalendarDate,
MakeDate(Subfield(Technical_Start_Date, '-', 1), Subfield(Technical_Start_Date, '-', -1)) as TechnicalDate
;
Load * Inline [
Calendar_Month_Code, Technical_Start_Date, Key_EMS, Part_Group, Equipment, Removal_Flag, Install_Flag
2012-01, 2011-12, 10105, MCR 0508, 10105, 0, 0
2012-02, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-03, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-04, 2011-09, 10105, MCR 0508, 10105, 0, 0
2012-01, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-02, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-03, 2011-09, 10106, MCR 0509, 10110, 0, 0
2012-04, 2011-09, 10106, MCR 0509, 10110, 0, 0
];
Hope i got it right, let me know.
K
Hi Kalmer,
Can you please see the post i.e.
Liron helped me with his logic but only one logic is pending.
Please let me know if you need anything more.