Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Making Buckets while loading data

Hi,

I have  a table  for example

Paid_date
Due_date
DayDifference
12-Apr-201212-Apr-2012 0
25-Mar-201225-Apr-201230
27 -Apr-201224-Jun-201263

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.                        

7 Replies
Employee
Employee

Re: Making Buckets while loading data

Maybe "Class" function can help you

Read your manual about this function

Re: Making Buckets while loading data

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.


talk is cheap, supply exceeds demand

Re: Making Buckets while loading data

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

Not applicable

Re: Making Buckets while loading data

fry :

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

munnareddy
Contributor

Re: Making Buckets while loading data

Hi anand,

i want all different buckets make into one Dimension.

Please find below excel.

1-30 Days31-60 Days61-90 Days91-120 Days121-360 DaysOver 360 Days
000000
00-24,223.94000
1,197.6000000
1,437.1200000
000000
000000
000000
000000
000000
000000
00000-408.96
0000820.80
001,306.80000
003,294.72000
002,903.04000
03,294.720000
02,747.520000
02,747.520000
05940000
05940000
01,451.520000
0967.680000
001,446.70000
0178.20000
33,642.6800000
2,229.4800000
806.400000
11,736.0000000
5,952.9600000
59900000

i want like this way

Days.PNG.png

Please help on this ...

Regards

Srinivas

Employee
Employee

Re: Making Buckets while loading data

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

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, 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,

];

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;

Community Browser