topic RESIDENT and LEFT JOIN in QlikView

RESIDENT and LEFT JOIN

Tue, 16 Nov 2010 17:27:52 GMT

Hello Everydody;

I have a good SQL script but I don't arrive to translate it in qlikview.

This is my sql

select
a.*,
b.CA as CA_N_1_JaJ,
b.Nb_Client as Nb_Client_N_1_JaJ
from
Table1 a
left join
Table1 b
on (a.Code_Client = b.Code_Client
and a.Temps_Jour_Nom = b.Temps_Jour_Nom
and (b.ChampDate between a.ChampDate - 370 and a.ChampDate- 360)
)

I've tried with RESIDENT and LEFT JOIN but I'm not arrived to OK result.

Can you help me?

Think you for tour help

RESIDENT and LEFT JOIN

Tue, 16 Nov 2010 17:45:42 GMT

Did you try to connect on your database then run the same sql statement ?

Such as

SQL

Rgds,

Sébastien

RESIDENT and LEFT JOIN

parpatra — Tue, 16 Nov 2010 18:32:35 GMT

Hey Sorry for this but I have a simpe question whcih is related to this...

When I do a left join between 2 resident tables, is there a way I can add the condition as we do in SQL ..

ex..Left join on a.fieldsname=b.fieldname?

Or does qlikview do it automatically based on column names?

I ahve a requirement to join 2 resident tables based on 3 conditions.

Thanks a lot!!

RESIDENT and LEFT JOIN

pover — Tue, 16 Nov 2010 22:09:57 GMT

The QlikView Join is done solely based on common column names. There is usually no problem with this since you just change the names of the columns in order to perform the join properly.

As Sébastien posted, it is easier to directly use SQL that is already working properly, but if you want to do something similiar in QlikView to the SQL posted by Lna you are going to have to use the Intervalmatch() function because one of the conditions is a range.

For example

Table1:
LOAD *, ChampDate as ChampDate_From, ChampDate as ChampDate_To;
SQL Select *
From Table1;

Drop field ChampDate from Table1;

Table2:
LOAD Code_Client,
Temps_Jour_Nom,
ChampDate ,
CA as CA_N_1_JaJ,
Nb_Client as Nb_Client_N_1_JaJ;
SQL Select Code_Client,Temps_Jour_Nom,ChampDate,CA,Nb_Client
From Table2;
Inner Join
IntervalMatch ( ChampDate ,Code_Client,Temps_Jour_Nom) Load ChampDate_From, ChampDate_To, Code_Client,Temps_Jour_Nom Resident Table1;

Left Join (Table1)
Load *
Resident Table2;

Drop Table Table1;

Regards.

RESIDENT and LEFT JOIN

johnw — Tue, 16 Nov 2010 22:17:32 GMT

(Bah, beaten to it. Ah, well. I'm posting anyway. 😛 That we basically have the same solution makes me more confident in it, at least.)

QlikView does it automatically based on column names. You can add conditions, but they're conditions on the load of the second table, not strictly conditions on the join itself, so you can't (for instance) reference the table you're joining to.

And this, of course, explains why we can't do this left join as simply as we would be able to in SQL. Instead, I believe we would create new fields for the date range, and then intervalmatch to that date range. Untested, but perhaps like this:

MyTable:
LOAD *
,date(ChampDate-370) as FromDate
,date(ChampDate-360) as ToDate
;
SQL
SELECT *
FROM Table1
;
B:
LOAD
Code_Client
,Temps_Jour_Nom
,ChampDate as BChampDate
,CA as CA_N_1_JaJ
,Nb_Client as Nb_Client_N_1_JaJ
RESIDENT MyTable
;
LEFT JOIN (MyTable)
INTERVALMATCH (BChampDate, Code_Client, Temps_Jour_Nom)
LOAD FromDate, ToDate, Code_Client, Temps_Jour_Nom
RESIDENT B
;
DROP FIELDS
BChampDate
,FromDate
,ToDate
;

RESIDENT and LEFT JOIN

Wed, 17 Nov 2010 17:03:57 GMT

Hello!

Thank you for all your responses but I'm tried all the day and I'm not arrived to have a good results!

Have you any idea?

Thank you very much!

RESIDENT and LEFT JOIN

pover — Wed, 17 Nov 2010 17:07:08 GMT

What problem or error are you experiencing?

Regards.

RESIDENT and LEFT JOIN

Thu, 18 Nov 2010 09:34:32 GMT

Hello Everybody, Finally, my last test is ok!

you have help me! very good!

i'm thank you for your help!

have a good day