Outer Joins Issue

jgarciaf106 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I am trying to create a QVD file out of 4 crosstables, as the information contained is similar I want to user outer Join to merge all tables to create on single table with all columns I need. I get an error of table not found:

Any thoughs?

Thanks in advanced.

Table1_TEMP:

// ----------------------------------------

// Create SQL select Statement

// ----------------------------------------

CrossTable([Call Date], [Call Details])

LOAD *

FROM

$(vG.ExtractPath)RH Contigo Database.xlsx

(ooxml, embedded labels, table is [N° LLAMADAS]);

Table2_TEMP:

// ----------------------------------------

// Create SQL select Statement

// ----------------------------------------

CrossTable([Call Attendance Date], [Call Attendance Details])

LOAD *

FROM

$(vG.ExtractPath)RH Contigo Database.xlsx

(ooxml, embedded labels, table is [TIEMPO ATENDIMENTO]);

Table3_TEMP:

// ----------------------------------------

// Create SQL select Statement

// ----------------------------------------

CrossTable([Logon Date], [Logon Details])

LOAD *

FROM

$(vG.ExtractPath)RH Contigo Database.xlsx

(ooxml, embedded labels, table is LOGON);

Table4_TEMP:

// ----------------------------------------

// Create SQL select Statement

// ----------------------------------------

CrossTable([Pausa Date], [Pausa Details])

LOAD *

FROM

$(vG.ExtractPath)RH Contigo Database.xlsx

(ooxml, embedded labels, table is PAUSA);

LET vL.TableName='RHC VoxAge';

LET vL.before=now();

[$(vL.TableName)]:

// ----------------------------------------

Load *

Resident Table1_TEMP;

// ----------------------------------------

Outer Join([$(vL.TableName)])

Load *

Resident Table2_TEMP;

// ----------------------------------------

Outer Join([$(vL.TableName)])

Load *

Resident Table3_TEMP;

// ----------------------------------------

Outer Join([$(vL.TableName)])

Load *

Resident Table4_TEMP;

// ----------------------------------------

Drop Tables Table1_TEMP, Table2_TEMP, Table3_TEMP, Table4_TEMP;

// ----------------------------------------

// Write out the QVD to the defined path

// ----------------------------------------

STORE [$(vL.TableName)] INTO '$(vL.QVDPath)\$(vL.TableName).qvd';

// ----------------------------------------

// Create statistical data

// ----------------------------------------

LET vL.after=now();

LET vL.TableNumber=$(vL.TableNumber)+1;

let vL.TableName_$(vL.TableNumber) ='$(vL.TableName)';

let vL.NoOfRows_$(vL.TableNumber) = NoOfRows('$(vL.TableName)');

let vL.NoOfFields_$(vL.TableNumber) = NoOfFields('$(vL.TableName)');

let vL.TableTime_$(vL.TableNumber) =time(Timestamp('$(vL.after)') - timestamp('$(vL.before)'),'hh:mm:ss');

// *****************************************;

// Drop table;

//*****************************************;

DROP Table [$(vL.TableName)];

trace 'Dropped Table $(vL.TableName)';

//-----------------------------------------

Re: Outer Joins Issue

jwjackso — Mon, 12 Mar 2018 22:05:03 GMT

If the fields for Table1_TEMP, Table2_TEMP, Table3_TEMP and Table4_TEMP are identical, Qlik will only create Table1_TEMP with records from all 4 tables.

Re: Outer Joins Issue

Digvijay_Singh — Tue, 13 Mar 2018 04:11:27 GMT

Can you confirm if you have shared the complete script? Can you use Exit Script; before the statement -

LET vL.TableName='RHC VoxAge';

and see if you have all the 4 temp tables created?

As I see nothing wrong after that, so looks like something is wrong while creation of temp tables.

Re: Outer Joins Issue

techvarun — Tue, 13 Mar 2018 04:44:30 GMT

Add noconcatenate before

[$(vL.TableName)]:

// ----------------------------------------

noconcatenate Load *

Resident Table1_TEMP;

Since the structure is same it won't consider RHC VoXAge as the table name

Re: Outer Joins Issue

jonathandienst — Tue, 13 Mar 2018 04:49:36 GMT

That should read NoConcatenate (one word):

[$(vL.TableName)]:

NoConcatenate

LOAD

...

Re: Outer Joins Issue

techvarun — Tue, 13 Mar 2018 04:54:16 GMT

Yeah My bad... Will edit it. Thanks Jonathan

topic Outer Joins Issue in QlikView

Outer Joins Issue

Re: Outer Joins Issue

Re: Outer Joins Issue

Re: Outer Joins Issue

Re: Outer Joins Issue

Re: Outer Joins Issue