topic Re: Left Join with ON clause in QlikView

Left Join with ON clause

Fri, 05 Apr 2013 08:05:21 GMT

Hi,

I have two tables with that structure:

Table1:

id | Numb | date |

Table2:

Numb | dateInit | dateEnd | Loc

I want to do a left join between this two tables ( Numb ) in order to add the Loc field to the Table1 when date >= dateInit AND date <= dateEnd

in MYSQL :

SELECT tb1.id,tb1.Numb,tb1.date,tb2.Loc FROM Table1 as tb1 LEFT JOIN Table2 as tb2 ON tb1.Numb = tb2.Numb AND tb1.date >= tb2.dateInit AND tb1.date <= tb2.dateEnd

Thanks in advance

Albert

Re: Left Join with ON clause

vikasmahajan — Fri, 05 Apr 2013 08:11:53 GMT

What is a issue It seems this sql is ok

Re: Left Join with ON clause

alexandros17 — Fri, 05 Apr 2013 08:13:33 GMT

try this

SELECT tb1.id,tb1.Numb,tb1.date,tb2.Loc FROM Table1 as tb1 LEFT JOIN Table2 as tb2 ON tb1.Numb = tb2.Numb

WHERE

tb1.date >= tb2.dateInit AND tb1.date <= tb2.dateEnd

Re: Left Join with ON clause

lironbaram — Fri, 05 Apr 2013 08:14:19 GMT

hi you should use interval match

i'll try to upload a demo later

Re: Left Join with ON clause

prasad_dumbre — Fri, 05 Apr 2013 08:19:58 GMT

Hi,

Refer This link for eg:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Re: Left Join with ON clause

Fri, 05 Apr 2013 08:53:05 GMT

Yes, i know the mYSQL sentenc is correct, but i want to do it on Qlikview Script.

Re: Left Join with ON clause

alexandros17 — Fri, 05 Apr 2013 08:59:59 GMT

TMP_Table:

LOAD id,Numb,date FROM Table1

left join

LOAD Numb,| dateInit, dateEnd, Loc FROM Table2

MyTable:

noconcatenate

LOAD

resident TMP_Table

where

date >= dateInit AND date <= dateEnd

Drop table TMP_Table;