topic Re: Interval Match or Inline load in QlikView

Interval Match or Inline load

ashwanin — Tue, 24 Sep 2013 05:06:11 GMT

Hi QV Users,

I am facing a problem in linking the values .

i have below values.

from	to	area
1	999	Delhi
1000	2000	Nagaland
2021	3455	Punjab

and in another table i have the below data:

Data	area
555	Delhi
100000	orissa
1111	Nagaland
2040	Punjab
2045	Punjab

here i have to map both tables as per the range. ie values From AND To with Data in table 2

Please advise how is it to be done?

Thanks in advance.

Re: Interval Match or Inline load

er_mohit — Tue, 24 Sep 2013 06:19:13 GMT

Try this code and paste in your script reload it here to use interval match match it creates synchronization key i use the join concept to remove it have a look

Test:

LOAD * Inline [

from, to, area

1, 999, Delhi

1000, 2000, Nagaland

2021, 3455, Punjab

];

Data:

LOAD * Inline [

Data, area

555, Delhi

100000, orissa

1111, Nagaland

2040, Punjab

2045, Punjab

];

Left join(Data)

Intervalmatch(Data)

LOAD from, to

RESIDENT Test;

Left join (Data)

LOAD * resident Test;

drop table Test;

hope it helps you

Re: Interval Match or Inline load

ashwanin — Tue, 24 Sep 2013 07:08:12 GMT

Hi Mohit,

thanks for the below.

Can you please help me , if in case i have only Data value in table 2 ?

Because in some cases here i found that the Area is not given correctely its ias !!Delhi continet, delhi d.

So instead of Area can i map Value ? is it possible with Interval Match?

Re: Interval Match or Inline load

Tue, 24 Sep 2013 07:18:44 GMT

Looking at your data set it seems that every area will have a correct and matching interval table.

Based on this it seems that it would be easier just to do a left join on area. So take Mohits reply to you and just do this

Test:

LOAD * Inline [

from, to, area

1, 999, Delhi

1000, 2000, Nagaland

2021, 3455, Punjab

];

Data:

LOAD * Inline [

Data, area

555, Delhi

100000, orissa

1111, Nagaland

2040, Punjab

2045, Punjab

];

Left join (Data)

LOAD *

resident Test;

drop table Test;

Re: Re: Interval Match or Inline load

Tue, 24 Sep 2013 08:02:28 GMT

Yes you can, attached is Mohit Sahrma script with a small change

Re: Interval Match or Inline load

qlikpahadi07 — Tue, 24 Sep 2013 11:48:46 GMT

for this you clean you Area Column correctly using Mapping tables or you can use Numeric Key linked with Master Area then apply the Interval match Function