LEFT JOIN LOAD FOR A GAP

[lmenendez_grupo_pinero]

Hi,

I wonder if there is possible to make a left join load with gaps instead of finding the exact value.

For exemple I can join table like :

100,A

110,B

120,C

130,D ...

but if I have a value '104' I wil not have a letter.

I am looking for the way to left join load letter 'A' to all the values betwen 100 and 109, letter 'B' to all teh values betwen 110 and 119 , and so on.

Is it possible?

Thank you

[stevejoyce]

Take a look at IntervalMatch ‒ QlikView.

[lmenendez_grupo_pinero]

Quite difficult to understand the command but I will try it , Thank you very much.

[MarcoWedel]

Hi,
one example:

tabLetterTemp:
LOAD * Inline [
Val, Letter
100,A
110,B
120,C
130,D
150,E
180,F
];

tabLetter:
LOAD Val as MinVal,
     Alt(Previous(Val)-1, 1000) as MaxVal,
     Letter    
Resident tabLetterTemp
Order By Val Desc;

DROP Table tabLetterTemp;

table1:
LOAD Ceil(Rand()*100+100) as Value
AutoGenerate 50;

Left Join (table1)
IntervalMatch (Value)
LOAD MinVal, MaxVal     
Resident tabLetter;

Left Join (table1)
LOAD * Resident tabLetter;

DROP Fields MinVal, MaxVal From table1;

hope this helps

Marco

[lmenendez_grupo_pinero · Accepted solution]

Espectacular explanation for a begginer like me, thank you very much Marco.

[MarcoWedel]

You're welcome.
Please close your thread by accepting an answer as solution.
Thanks
Marco