topic Re: Making Buckets while loading data in QlikView

Making Buckets while loading data

Thu, 22 Nov 2012 12:20:16 GMT

Hi,

I have a table for example

Paid_date	Due_date	DayDifference
12-Apr-2012	12-Apr-2012	0
25-Mar-2012	25-Apr-2012	30
27 -Apr-2012	24-Jun-2012	63

I want to make buckets by using DayDifference coloumn example

DayDifference DiffBucket

0 0-30 days

31 31-60 days

63 61-90 days

and so on .. buckets which i want is

0-30 days

31-60 days

61-90 days

91-180 days

180 and above

how can i do it while loading the data without using if() statement any idea ?

Thanks & Regards in advance

s_tushar.

Re: Making Buckets while loading data

Clever_Anjos — Thu, 22 Nov 2012 12:30:18 GMT

Maybe "Class" function can help you

Read your manual about this function

Re: Making Buckets while loading data

Gysbert_Wassenaar — Thu, 22 Nov 2012 12:48:00 GMT

In this case the intervalmatch function probably is a better option than the class function. Particularly since you have unequal intervals. The class function can only generate buckets of the same size. See this discussion for more information and examples.

Re: Making Buckets while loading data

its_anandrjs — Thu, 22 Nov 2012 12:57:59 GMT

Hi,

Create Ageing bucket or other wise use interval match for this

Create ageing like

if(DayDifference >= 0 and DayDifference <= 30 , '0-30 days',

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

if(DayDifference >= 61 and DayDifference <= 90, '61-90 days' ))) as Ageing

Regards,

Anand

Re: Making Buckets while loading data

Thu, 22 Nov 2012 14:26:05 GMT

fry :

floor(num(due-paid)/30)*30&' - '&(floor(num(due-paid)/30)+1)*30&' days' as DiffBucket

Re: Making Buckets while loading data

Srinivas — Thu, 29 May 2014 12:20:18 GMT

Hi anand,

i want all different buckets make into one Dimension.

Please find below excel.

1-30 Days	31-60 Days	61-90 Days	91-120 Days	121-360 Days	Over 360 Days
0	0	0	0	0	0
0	0	-24,223.94	0	0	0
1,197.60	0	0	0	0	0
1,437.12	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	0
0	0	0	0	0	-408.96
0	0	0	0	820.8	0
0	0	1,306.80	0	0	0
0	0	3,294.72	0	0	0
0	0	2,903.04	0	0	0
0	3,294.72	0	0	0	0
0	2,747.52	0	0	0	0
0	2,747.52	0	0	0	0
0	594	0	0	0	0
0	594	0	0	0	0
0	1,451.52	0	0	0	0
0	967.68	0	0	0	0
0	0	1,446.70	0	0	0
0	178.2	0	0	0	0
33,642.68	0	0	0	0	0
2,229.48	0	0	0	0	0
806.4	0	0	0	0	0
11,736.00	0	0	0	0	0
5,952.96	0	0	0	0	0
599	0	0	0	0	0

i want like this way

Please help on this ...

Regards

Srinivas

Re: Making Buckets while loading data

Clever_Anjos — Sun, 01 Jun 2014 01:33:57 GMT

I can´t see the excel file

You could use something like this

LOAD

if(DateField < 31, dual('1-30 Days',1),

if(DateField < 61, dual('31-60 Days',2),

if(DateField < 91, dual('61-90 Days',3),

if(DateField < 121, dual('91-120 Days',4),

if(DateField < 360, dual('121-360 Days',5),dual('Over 360 Days',6)

))))

Re: Making Buckets while loading data

its_anandrjs — Sun, 01 Jun 2014 05:39:17 GMT

I did not find any excel file but from attached sample data please check the load statement which is a crosstable load statement.

tmpSource:

LOAD * Inline

[

1-30 Days, 31-60 Days, 61-90 Days, 91-120 Days, 121-360 Days, Over 360 Days

0, 0, 0, 0, 0, 0

0, 0, -24,223.94, 0, 0, 0

1,197.60, 0, 0, 0, 0, 0

1,437.12, 0, 0, 0, 0, 0

0, 0, 0, 0, 0, 0

0, 0, 0, 0, 0, -408.96

0, 0, 0, 0, 820.8, 0,

0, 0, 1,306.80, 0, 0, 0

0, 0, 3,294.72, 0, 0, 0

0, 0, 2,903.04, 0, 0, 0

0, 3,294.72, 0, 0, 0, 0

0, 2,747.52, 0, 0, 0, 0

0, 594, 0, 0, 0, 0,

0, 1,451.52, 0, 0, 0, 0

0, 967.68, 0, 0, 0, 0,

0, 0, 1,446.70, 0, 0, 0

0, 178.2, 0, 0, 0, 0,

33,642.68, 0, 0, 0, 0, 0

2,229.48, 0, 0, 0, 0, 0

806.4, 0, 0, 0, 0, 0,

11,736.00, 0, 0, 0, 0, 0

5,952.96, 0, 0, 0, 0, 0

599, 0, 0, 0, 0, 0,

];

Crosstable (Bucket,Sales,1) Load

1 as Dummy,

[1-30 Days],

[31-60 Days],

[61-90 Days],

[91-120 Days],

[121-360 Days],

[Over 360 Days] Resident tmpSource;

DROP Table tmpSource;

DROP Field Dummy;