topic Re: Need to calculate Age in QlikView

Need to calculate Age

bharatkishore — Thu, 06 Sep 2018 11:20:32 GMT

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

Re: Need to calculate Age

Kalmer — Thu, 06 Sep 2018 12:12:36 GMT

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;

Re: Need to calculate Age

bharatkishore — Thu, 06 Sep 2018 12:27:53 GMT

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

Re: Need to calculate Age

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

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

Re: Need to calculate Age

bharatkishore — Fri, 07 Sep 2018 12:11:51 GMT

Hi Kalmer,

Can you please see the post i.e.

Re: Peek or Previous

Liron helped me with his logic but only one logic is pending.

Please let me know if you need anything more.