Making Buckets while loading data

[#1]

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.                        

[Clever_Anjos]

Maybe "Class" function can help you

Read your manual about this function

[Gysbert_Wassenaar]

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.

[its_anandrjs]

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

[#5]

fry :

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

[Srinivas]

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

[Clever_Anjos]

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)

))))

[its_anandrjs]

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;