Omit dimension when not found

[#1]

Hi All,

using a loop I need to load data from tables where some dimensions are common between the tables, some are not. What should I put into load code so that all tables can be loaded by a single load (i.e. when dimension was not found load all other without raising an error)? In the below example I'd like to load all data from Table1 and Table2, using a single load code, to get Table3.

Regards,

Przemek

INPUT
Table1: 
FirstName, Color
John, Blue
Table2: 
Color, City
Red, Berlin
OUTPUT
Table3:
FirstName, Surname, City
John, Blue, Null
Null, Red, Berlin

[hic]

Table1:

Load FirstName, Color From Table1;

Concatenate (Table1)

Load Color, City From Table2;

HIC

[swuehl]

Not sure what you mean with 'loaded by a single load', I think you need two load statements when you adressing two different table sources.

Besides this, I think you can just load the two tables, QV will link the tables by Color field and should handle your request properly.

Table1:

LOAD * INLINE  [

FirstName, Color

John, Blue

];

Table2:

LOAD * INLINE [

Color, City

Red, Berlin

];

Or just add a JOIN before the second load, to join both tables, resulting in one final table:

Table1:

LOAD * INLINE  [

FirstName, Color

John, Blue

];

Table2:

JOIN LOAD * INLINE [

Color, City

Red, Berlin

];

edit: Just noticed Henrics suggestions, which is another alternative. I think it depends how you want to handle the records in both tables that may have same COLOR values.

[#4]

Not that easy thing - there are thousands of tables. I look for a kind of a metafunction (let's call it NotFound()) that would check if dimension exists and omit it if not e.g.:

FOR i=1 to 10000

LET Table = 'Table'&$(i);

LOAD

     If(NotFound(FirstName), '', FirstName) AS FirstName,

     If(NotFound(Color), '', Color) AS Color,

     If(NotFound(City), '', City) AS City

FROM $(Table);

NEXT

[#5]

Well, the thing is I need to do it in a single statment...Otherwise will spend days creating statements.

[hic · Accepted solution]

Or if you want ti do it in a loop, you can do something like:

Set vConcatenate =  ;

For each vFile in FileList('C:\directory\*.csv')

   $(vConcatenate)

   Load * from [$(vFile)] ... ;

   Set vConcatenate = Concatenate ;

Next vFile

HIC

[#7]

Perfect! Thank you very much, Henric.