2 Replies Latest reply: Mar 17, 2016 10:05 AM by Sangram Reddy RSS

    Using a bucket

    Madeleine Josefsson

      Hi,

      I have the following bucket in my script that I want to use as a dimension:

      Bucket:

      Load * Inline [Bucket, Start,End

      '1-30 days', 1 ,30

      '31-60 days', 31 ,60

      '61-90 days', 61 ,90

      '90 days +', 91 , 9999

      'Not due', -99999 ,0

      ];

       

      How can I re-write it so that it uses the interval I created, DueDays?

      LOAD

          AlfaCompany,

          InvoiceNumber,

          InvoiceSum,

          InvoiceDueDate,

          Interval(InvoiceDueDate - Today(),'d') as DueDays,

          CustomerNumber,

          InvoiceCurrency,

          CustomerName,

          InvoiceSumCompanyCurrency,

          AROfficialSerNr,

          ARARRebDate2,

          InvoiceSumGroupCurrency

       

      Thanks!

        • Re: Using a bucket
          Sunny Talwar

          Something like this:

           

          Bucket:

          Load * Inline [Bucket, Start,End

          '1-30 days', 1 ,30

          '31-60 days', 31 ,60

          '61-90 days', 61 ,90

          '90 days +', 91 , 9999

          'Not due', -99999 ,0

          ];

           

          Table:

          LOAD * Inline [

          DueDays

          15

          80

          32

          100

          32

          23

          2

          76

          -100

          9998

          10000

          ];

           

          Left Join (Table)

          IntervalMatch(DueDays)

          LOAD Start,

            End

          Resident Bucket;

           

          Left Join (Table)

          LOAD *

          Resident Bucket;

           

          DROP Table Bucket;

           

          Here you would replace the second inline table with your table

           

          Capture.PNG

          • Re: Using a bucket
            Sangram Reddy

            Hi Madeleine,

             

            You can even do this:

             

            Bucket:

            Load * Inline [

            Bucket, Start,End,Code

            '1-30 days', 1 ,30,1

            '31-60 days', 31 ,60,2

            '61-90 days', 61 ,90,3

            '90 days +', 91 , 9999,4

            'Not due', -99999 ,0,5

            ];

             

            Table:

            LOAD * Inline [

            DueDays

            15

            80

            32

            100

            32

            23

            2

            76

            -100

            9998

            10000

            ];

             

            [Actual Table]:

            Load DueDays,

                    if(DueDays > 0 and DueDays < 31, 1,

                    if(DueDays > 30 and DueDays < 61,2,

                   if(DueDays > 60 and DueDays < 91,3,

                   if(DueDays > 90,4,

                   if(DueDays < 0,5))))) as Code

                   resident Table;


            Drop table [Table];

             

             

            This will create a data model with two tables and you will be able to achieve calculations you need.

             

            Thanks,

            Sangram.