Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
marieclaire
New Contributor III

What is the syntax for an outer join ?

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

1 Solution

Accepted Solutions
MVP
MVP

Re: What is the syntax for an outer join ?

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

2015-09-25 #1.png

14 Replies
MVP
MVP

Re: What is the syntax for an outer join ?

QlikView supports OUTER JOIN by using the prefix OUTER:

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



MVP
MVP

Re: What is the syntax for an outer join ?

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

2015-09-25 #1.png

MVP
MVP

Re: What is the syntax for an outer join ?

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

jolivares
Valued Contributor

Re: What is the syntax for an outer join ?

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.

evan_kurowski
Valued Contributor

Re: What is the syntax for an outer join ?

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);

marieclaire
New Contributor III

Re: What is the syntax for an outer join ?

Thank you very much Petter.

It is exactly what i expected.

And there is no more problem of date.

Thank you !

marieclaire
New Contributor III

Re: What is the syntax for an outer join ?

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 !

MVP
MVP

Re: What is the syntax for an outer join ?

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

ecolomer
Honored Contributor II

Re: What is the syntax for an outer join ?

Community Browser