topic Re: IntervalMatch including field in fact table in QlikView

IntervalMatch including field in fact table

Pedro_Rodriguez — Mon, 17 Jul 2017 10:14:01 GMT

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!

Re: IntervalMatch including field in fact table

balar025 — Mon, 17 Jul 2017 11:14:24 GMT

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

Re: IntervalMatch including field in fact table

effinty2112 — Mon, 17 Jul 2017 12:35:49 GMT

Hi Pedro,

Add this to the script:

Left Join(Customers)

LOAD Age, Class Resident Classes;

DROP Table Classes;

cheers

Andrew

Re: IntervalMatch including field in fact table

Pedro_Rodriguez — Tue, 18 Jul 2017 07:13:50 GMT

Thank you both guys, really appreciate it