Interval Match or Inline load

[ashwanin]

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.

[er_mohit · Accepted solution]

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

[ashwanin]

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?

[#4]

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;

[#5]

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

[qlikpahadi07]

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