topic Re: LEFT JOIN LOAD FOR A GAP in QlikView

LEFT JOIN LOAD FOR A GAP

lmenendez_grupo_pinero — Fri, 19 Nov 2021 14:23:30 GMT

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

Re: LEFT JOIN LOAD FOR A GAP

stevejoyce — Fri, 19 Nov 2021 14:30:50 GMT

Take a look at IntervalMatch ‒ QlikView.

Re: LEFT JOIN LOAD FOR A GAP

lmenendez_grupo_pinero — Fri, 19 Nov 2021 14:45:50 GMT

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

Re: LEFT JOIN LOAD FOR A GAP

MarcoWedel — Fri, 19 Nov 2021 16:50:53 GMT

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

Re: LEFT JOIN LOAD FOR A GAP

lmenendez_grupo_pinero — Mon, 22 Nov 2021 09:07:20 GMT

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

Re: LEFT JOIN LOAD FOR A GAP

MarcoWedel — Mon, 22 Nov 2021 21:24:18 GMT

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