Using a bucket

[Anonymous]

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!

[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

[reddy-s]

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.