IntervalMatch including field in fact table

[Pedro_Rodriguez]

Hello,

Having the following data:

Classes:

LOAD * INLINE [

    Class, BeginAge, EndAge

    18-25, 18, 25

    26-35, 26, 35

    36-45, 36, 45

    46-65, 46, 65

];

Customers:

LOAD * INLINE [

    ID, Name, Age, Accident

    1, Jan, 18, 1

    2, Klaas, 22, 0

    3, Kjeld, 46, 0

    4, Jochem, 35, 1

    5, Robin, 34, 0

    6, Jeroen, 29, 1

    7, Henk, 50, 1

    8, Piet, 55, 1

    9, Bram, 60, 1

];

With this code I assign age range to each person:

Inner JOIN (Classes)

IntervalMatch (Age)

Load Distinct

  BeginAge,

  EndAge

Resident Classes;

But what I want to achieve is only ONE table containing:

Customers_Def:

    ID, Name, Age, Accident, Class

    1, Jan, 18, 1, 18-25

    2, Klaas, 22, 0, 18-25

    3, Kjeld, 46, 0, 46-65

    4, Jochem, 35, 1, 26-35

    5, Robin, 34, 0, 26-35

    6, Jeroen, 29, 1, 26-35

    7, Henk, 50, 1, 46-65

    8, Piet, 55, 1, 46-65

    9, Bram, 60, 1, 46-65

*In my data there's no chance of one person in two age classes

Thanks a lot in advance!

[balar025 · Accepted solution]

Hi ,

You can use below code.

Inner JOIN (Classes)

IntervalMatch (Age)

Load Distinct

  BeginAge,

  EndAge

Resident Classes;

join

Load *

Resident Classes;

Drop table Classes;

Drop field  BeginAge,  EndAge;

Regards,

Ravi Balar

[effinty2112]

Hi Pedro,

                    Add this to the script:

Left Join(Customers)

LOAD Age, Class Resident Classes;

DROP Table Classes;

cheers

Andrew

[Pedro_Rodriguez]

Thank you both guys, really appreciate it