Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!
I am trying to load multiple tables with the same information in all of them, an then Concatenate them. However, the column names are different.
For example:
One set of tables has columns: Article_Num, Description, Line_Num....
While the other set has columns; Art. , Desc. , Lin....
There is no way to tell wich tale is which by the file name, all I know is that they are in the same and have "Stock" in the file name. There are also a lot of them (can´t go one by one).
I am using a for loop which iterates through every file with "*Stock*" in the name and then
CONCATENATE(STOCK)
LOAD
Article_Num,
Description,
Line_Num
From.....
However, when I reach a table that has Art. instead of Article_Num it fails.
Any way I can adapt the table from the script to read both possible column names?
Thank you,
Federico Arribas.
This is what I did at the end: Load the full table to identify which type of table it is, and then create variables that change depending on a condition such as the header or name of dimensions.
Set vLoadSpace = '(txt, codepage is 28591, embedded labels, delimiter is |, msq, header is ' & (vHeaderSize) & lines)';
set vHeaderSize = 4;
let vrtLoadSpace = $(vLoadSpace);
TABLE_TYPE:
LOAD
*
FROM [$(vFile)]
$(vrtLoadSpace);
let vNumOfFields = NoOfFields('TABLE_TYPE');
// Dependiendo del tipo de tabla, las dimensiones y el header van a ser diferentes.
let vProductID = 'Art.';
let vDescription= 'Desc.';
if vNumOfFields = 1 then
let vHeaderSize = 5;
let vrtLoadSpace = $(vLoadSpace);
ElseIf vNumOfFields = 13 then
let vHeaderSize = 4;
let vrtLoadSpace = $(vLoadSpace);
let vProductID = 'Article_Number';
let vImporte = 'Description';
Else
//Throw exception?
trace $(vFile) is different;
EndIf;
// Cargamos tabla usando variables que dependen del tipo y la concatamos con la tabla original
CONCATENATE(STOCK)
LOAD
$(vProductID) as ProductID,
$(vDescription) as Description
......
FROM [$(vFile)]
$(vrtLoadSpace);
You may try it with ALIAS: Alias | Qlik Cloud Help
Hi @svendsenfe , you can rename fields to make them equal, with that, and if all the field have the same name, it is going to be an auto concatenate, otherwise you can add CONCATENATE between loads.
example :
Load
Article_Num,
Description,
Line_Num,
....
FROM YourSource1;
CONCATENATE //optional , because if you rename every field, you can make that the both load have the same fields, and structure.
Load
Art as Article_Num,
Desc as Description,
Lin as Line_Num,
....
FROM YourSource2;
exactly, using ALIAS (as), that is renaming fields
Thank you Marcus.
I did not know about Alias, they seems to be really helpful and a good solution for the problem stated.
However, it seems like one set of tables have the header in row 4 and the other in row 5.
What I was thinking is to load everything in one table:
TABLE_TYPE:
LOAD
*
FROM [$(vFile)]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq, header is 5 lines);
and if the parameters match (can find a dimension called Art. for example) call another funcion to concate in the main table one way. If it can´t find the parameter (finds Article_Number) call the other function to concate the other way
CALL Load_Table_One _Way()
CONCATE(STOCK)
LOAD
......
FROM....
(txt, codepage is 28591, embedded labels, delimiter is '|', msq, header is 5 lines);
and
CALL Load_Table_Other _Way()
CONCATE(STOCK)
LOAD
......
FROM....
(txt, codepage is 28591, embedded labels, delimiter is '|', msq, header is 4 lines);
What do you think? Will let you know how it works.
Best regards,
Federico Arriba.
Thank you for you answer Fabian,
Problem I see with this is that there are multiple tables and can´t differenciate one from another unless loaded
This is what I did at the end: Load the full table to identify which type of table it is, and then create variables that change depending on a condition such as the header or name of dimensions.
Set vLoadSpace = '(txt, codepage is 28591, embedded labels, delimiter is |, msq, header is ' & (vHeaderSize) & lines)';
set vHeaderSize = 4;
let vrtLoadSpace = $(vLoadSpace);
TABLE_TYPE:
LOAD
*
FROM [$(vFile)]
$(vrtLoadSpace);
let vNumOfFields = NoOfFields('TABLE_TYPE');
// Dependiendo del tipo de tabla, las dimensiones y el header van a ser diferentes.
let vProductID = 'Art.';
let vDescription= 'Desc.';
if vNumOfFields = 1 then
let vHeaderSize = 5;
let vrtLoadSpace = $(vLoadSpace);
ElseIf vNumOfFields = 13 then
let vHeaderSize = 4;
let vrtLoadSpace = $(vLoadSpace);
let vProductID = 'Article_Number';
let vImporte = 'Description';
Else
//Throw exception?
trace $(vFile) is different;
EndIf;
// Cargamos tabla usando variables que dependen del tipo y la concatamos con la tabla original
CONCATENATE(STOCK)
LOAD
$(vProductID) as ProductID,
$(vDescription) as Description
......
FROM [$(vFile)]
$(vrtLoadSpace);