Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
And move the join to the end of your script like this:
QlikView supports OUTER JOIN by using the prefix OUTER:
outer join (tab1) LOAD * FROM filename.csv;
And move the join to the end of your script like this:
Could you explain a bit more what your problems with dates are?
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.
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
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
(
Thank you very much Petter.
It is exactly what i expected.
And there is no more problem of date.
Thank you !
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 !
Glad to help Please close the thread by marking it answered.
See this: