topic Re: intervalmatch with multiple intervals in QlikView

intervalmatch with multiple intervals

Mon, 05 Nov 2012 20:20:09 GMT

Hope someone can help me on this one. I have table with income intervals, however there are different sets of intervals depending on the number of persons in the household. For example:

Key:

Persons in Household/IncomeStart/IncomeEnd/IntervalName

1/24000/30000/<30%

1/30001/40000/31-40%

1/40001/50000/41-50%

2/25000/31000/<30%

2/31001/41000/31-40%

2/41001/51000/41-50%

and so on...

I have another table that has persons in household and incomes:

Population:

Persons in Household/IncomeLevel

1/24020

2/45000

1/45500

4/35000

and so on...

how do i script an intervalmatch that will match the IntervalName in the Key table to the IncomeLevel in the Population table, based on persons in household?

Thanks

Re: intervalmatch with multiple intervals

swuehl — Mon, 05 Nov 2012 22:32:05 GMT

Look into the extended syntax of intervalmatch, where you can add a [Person in Household] as key to the interval match:

Key:

LOAD * INLINE [

Persons in Household/IncomeStart/IncomeEnd/IntervalName

1/24000/30000/<30%

1/30001/40000/31-40%

1/40001/50000/41-50%

2/25000/31000/<30%

2/31001/41000/31-40%

2/41001/51000/41-50%

] (delimiter is '/');

Population:

LOAD * INLINE [

Persons in Household/IncomeLevel

1/24020

2/45000

1/45500

4/35000

] (delimiter is '/');

IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

Re: intervalmatch with multiple intervals

Mon, 05 Nov 2012 23:25:47 GMT

I'm getting synthetic tables when I do this. I tried to work around that by joining:

left join (Population)

IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

and then, because the IntervalName field does not carry over, I did another join after the previous:

left join (Population)

load *

resident Key;

drop table Key;

However, there seems to be some data inconsistencies when I do this. Some fields are missing IntervalNames when they are clearly marked in the Key table. Anyone know what's going on?

Re: intervalmatch with multiple intervals

swuehl — Tue, 06 Nov 2012 08:42:55 GMT

The created synthetic table is ok, but if you want to get rid of the synthetic key, I think you can do it like

join IntervalMatch (IncomeLevel,[Persons in Household]) LOAD IncomeStart, IncomeEnd, [Persons in Household] Resident Key;

Left join (Population) LOAD * resident Key;

drop table Key;

Not sure why you are missing data when they should have a link to IntervalName. Could you upload some data that demonstrate this issue?