7 Replies Latest reply: Jun 1, 2014 1:39 AM by Anand Chouhan RSS

    Making Buckets  while loading data

    Tushar Suvarna

      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.                        

        • Re: Making Buckets  while loading data
          Clever Anjos

          Maybe "Class" function can help you

          Read your manual about this function

          • Re: Making Buckets  while loading data
            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.

            • Re: Making Buckets  while loading data
              Anand Chouhan

              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
                  M srinivas

                  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

                    • Re: Making Buckets  while loading data
                      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)

                      ))))

                      • Re: Making Buckets  while loading data
                        Anand Chouhan

                        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;

                    • Re: Making Buckets  while loading data
                      Wojciech Parzyszek

                      fry :

                       

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