Outer Joins Issue

[jgarciaf106]

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

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

[jwjackso]

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.

[Digvijay_Singh]

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.

[techvarun]

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

[jonathandienst · Accepted solution]

That should read NoConcatenate (one word):

[$(vL.TableName)]:

NoConcatenate

LOAD

...

[techvarun]

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