Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Table1:
Load FirstName, Color From Table1;
Concatenate (Table1)
Load Color, City From Table2;
HIC
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.
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
Well, the thing is I need to do it in a single statment...Otherwise will spend days creating statements.
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
Perfect! Thank you very much, Henric.