topic Re: What is the syntax for an outer join ? in QlikView

What is the syntax for an outer join ?

marieclaire — Fri, 25 Sep 2015 18:02:03 GMT

Hello

A question of beginner : how can i make an outer join for these 2 tables ?

I tried to use the syntax

join (tab1) LOAD * FROM filename.csv

For each key (@CLE_Site), and each month, i want to have one table with all values.

I also have a problem with the date format of both tables.

Here is an exemple of dataset and qw.

Thank you for your help.

Marie-Claire

Re: What is the syntax for an outer join ?

petter — Fri, 25 Sep 2015 18:30:48 GMT

QlikView supports OUTER JOIN by using the prefix OUTER:

outer join (tab1) LOAD * FROM filename.csv;

Re: What is the syntax for an outer join ?

petter — Fri, 25 Sep 2015 18:33:03 GMT

And move the join to the end of your script like this:

Re: What is the syntax for an outer join ?

petter — Fri, 25 Sep 2015 18:36:12 GMT

Could you explain a bit more what your problems with dates are?

Re: What is the syntax for an outer join ?

jolivares — Fri, 25 Sep 2015 18:37:02 GMT

QV makes the relation with fields with the same name, in this case you need to have the same name for mois_visiteurs and moir, in order that QV knows that is the same column.

I recommend that you make a key with all fields to avoid the synthetic table-key.

@cle_site&annee&mois as cleannmo_Key

in the other questions about the date what they your refers to, i can't find, but if you want to get a date from

makedate(Annee,mois) as date

This generate a date for esch month.

Re: What is the syntax for an outer join ?

evan_kurowski — Fri, 25 Sep 2015 18:51:47 GMT

Hello MarieClaire,

As a possibility, QlikView field association function in a way that could be described as a full outer join between tables. Consider an approach without an explicit join?

Bilan:
LOAD
AutoNumberHash128 (d_site,d_time_year) AS %KEY, //key is compound using @CLE_Site + Anée
d_site as @CLE_Site,
d_time_year as Année,
num (d_time_month,00) as Mois,
m_visits,
m_page_views,
m_time_spent,
m_time_spent_per_pages,
m_entry_rate,
m_entering_visits,
m_time_spent_per_visits,
%Key_DataFeed_1F0D62CF450928BF   // Key to parent table: DataFeed
FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

Visiteurs:
LOAD
AutoNumberHash128(Sites,Left(Subfield(FileBaseName(),'_',3),4)) AS %KEY, //key is compound using @CLE_Site + Anée
//Sites as @CLE_Site, //** take from table Bilan
   //Left(Subfield(FileBaseName(),'_',3),4) as Année, //** take from table Bilan
   Mid(Subfield(FileBaseName(),'_',-1),5,2) as Mois_Visiteurs,
Visiteurs_Uniques_c,
Visiteurs_Uniques_tous
FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: What is the syntax for an outer join ?

marieclaire — Fri, 25 Sep 2015 19:21:40 GMT

Thank you very much Petter.

It is exactly what i expected.

And there is no more problem of date.

Thank you !

Re: What is the syntax for an outer join ?

marieclaire — Fri, 25 Sep 2015 19:26:18 GMT

Hello Evan,

I do not know what is the best way to do (a Join or a field association).

Both seem to fix my need.

And i will follow your way of doing key with

AutoNumberHash128(Sites,Left(Subfield(FileBaseName(),'_',3),4)) AS %KEY

Thank you !

Re: What is the syntax for an outer join ?

petter — Fri, 25 Sep 2015 19:58:37 GMT

Glad to help Please close the thread by marking it answered.

Re: What is the syntax for an outer join ?

ecolomer — Fri, 25 Sep 2015 20:50:26 GMT

See this:

Some Qlikview Joins best documents

Re: What is the syntax for an outer join ?

evan_kurowski — Sat, 26 Sep 2015 00:35:02 GMT

Hello Marie,

Actually looking at it once more, let's expand your keying system to an even finer granularity:

FROM: Site + Year

TO: Site + Year + Month

Bilan:
LOAD
AutoNumberHash128 (d_site,d_time_year & num (d_time_month,'00') ) AS %KEY, //key is compound using @CLE_Site + Année + Mois
d_site as @CLE_Site,
d_time_year as Année,
num (d_time_month,'00') as Mois,
m_visits,
m_page_views,
m_time_spent,
m_time_spent_per_pages,
m_entry_rate,
m_entering_visits,
m_time_spent_per_visits,
%Key_DataFeed_1F0D62CF450928BF // Key to parent table: DataFeed
FROM (XmlSimple, Table is [DataFeed/DataSet/Rows/Row]);

Visiteurs:
LOAD
AutoNumberHash128(Sites,Left(Subfield(FileBaseName(),'_',3),6)) AS %KEY, //key is compound using @CLE_Site + Année + Mois_Visiteurs

//Sites as @CLE_Site, //** take from table Bilan
//Left(Subfield(FileBaseName(),'_',3),4) as Année, //** take from table Bilan
//Mid(Subfield(FileBaseName(),'_',-1),5,2) as Mois_Visiteurs, //** take from table Bilan

Visiteurs_Uniques_c,
Visiteurs_Uniques_tous
FROM

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Re: What is the syntax for an outer join ?

qlikviewwizard — Sat, 26 Sep 2015 02:19:19 GMT

Hi,

Please check the below threads:

What are joins

Joins and Lookups

Re: What is the syntax for an outer join ?

marieclaire — Sat, 26 Sep 2015 04:37:24 GMT

Thank you very much Evan.

Thank you for your help.

Re: What is the syntax for an outer join ?

marieclaire — Sat, 26 Sep 2015 04:39:20 GMT

Thank you Arjun.

Sure, I will read it !

Re: What is the syntax for an outer join ?

marieclaire — Sat, 26 Sep 2015 04:41:32 GMT

That is very helpfull : everything on a single sheet !

Thank you very much Enrique.