Qlikview Load field

[danielnevitt]

Hi,

Is it possible to create a column on the Qlikview load statement which shows the Aging Bucket of overdues.?

My field name is Voucher Pay Due Dt and I would like to compare to Today's date and then create the following aging buckets:

<30 days

31-60 days

61-90 days

90+

Any help will be much appreciated.

Regards,

Daniel

[trdandamudi · Accepted solution]

Please see the below code as an example and hope this helps:

Source_Data:

Load * Inline [

ID,Voucher_Pay_Due_Dt

10,01/01/2016

20,02/05/2016

30,03/10/2016

40,04/01/2016

50,05/20/2016

60,06/11/2016

70,07/15/2016

80,12/01/2016

90,11/01/2016

100,01/10/2017

];

Final:

Load ID,Today()-Voucher_Pay_Due_Dt as NumOfDays,

If (Today()-Voucher_Pay_Due_Dt <30,'<30 days',

    If (Today()-Voucher_Pay_Due_Dt >31 and Today()-Voucher_Pay_Due_Dt <60,'>31 - 60 days',

    If (Today()-Voucher_Pay_Due_Dt >61 and Today()-Voucher_Pay_Due_Dt <90,'>61 - 90 days','90+'

)))   as Aging_Bucket

Resident Source_Data;

Drop Table Source_Data;

[trdandamudi]

You can also achieve this with the preceding load like below:

Source_Data:

Load ID,Today()-Voucher_Pay_Due_Dt as NumOfDays,

If (Today()-Voucher_Pay_Due_Dt <30,Dual('<30 days',1),

    If (Today()-Voucher_Pay_Due_Dt >31 and Today()-Voucher_Pay_Due_Dt <60,Dual('>31 - 60 days',2),

    If (Today()-Voucher_Pay_Due_Dt >61 and Today()-Voucher_Pay_Due_Dt <90,Dual('>61 - 90 days',3),Dual('90+',4)

)))   as Aging_Bucket

Inline [

ID,Voucher_Pay_Due_Dt

10,01/01/2016

20,02/05/2016

30,03/10/2016

40,04/01/2016

50,05/20/2016

60,06/11/2016

70,07/15/2016

80,12/01/2016

90,11/01/2016

100,01/10/2017

];

[annafuksa1]

you can use function class (overdues,30)

or if function

example

if(overdues =< 30, '<30 days',

if(overdues > 30 and overdues =< 60,'31-60 days',

if(overdues >60 and overdues =< 90,'61-90 days',

,'90+'

)))

[giakoum]

class is the correct way to go

[stabben23]

Or With intervalmatch:

Bucket:
Load * Inline [
RangeFrom, RangeTo, Bucket
0,30, <30
31,60, 31-60
61,90, 61-90
91,250, 90+
];

//Your facttable
Date:
Load
today()- "Voucher Pay Due Dt" as Range;
LOAD * Inline [
"Voucher Pay Due Dt"
2017-01-01
2017-01-02
2016-12-01
2016-11-10
2016-10-01
2017-01-15
];

Left Join(Date)
INTERVALMATCH (Range)
LOAD distinct
RangeFrom, RangeTo
Resident Bucket;

Left Join (Date)
Load * resident Bucket;

Drop Field RangeFrom, RangeTo

[MarcoWedel]

maybe helpful:

Grouping data in Qlikview

Need help?

Custom Filter(unusual range)

regards

Marco