Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

intervalmatch with multiple intervals

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

Tags (1)
3 Replies
MVP
MVP

Re: intervalmatch with multiple intervals

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;

Not applicable

Re: intervalmatch with multiple intervals

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?

MVP
MVP

Re: intervalmatch with multiple intervals

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?

Community Browser