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.
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;
Did you try to connect on your database then run the same sql statement ?
Such as
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) );
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;
(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 ;
