Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I'm doing a for each cicle to load multiple excel files. Although the names are very similar and the cycle works the tables have some differences. I want to know if I can concatenate everything directly in the loop or I have to do another loop to concatenate all the generated tables.
Thanks in advance
Best Regards
Bruno Paulo
No, you won't have to do this in a separate loop. You can concatenate those tables immediately, but you may end up with a data mess.
To solve the problem of QlikView deciding to create different tables anyway, because they look too different, add a prefix to the LOAD statement in your FOR EACH loop. If you store the prefix in a variable, you can modify it to be different for the first LOAD. Like in:
LET vPrefix = 'Table1:
NOCONCATENATE';
FOR EACH ...
:
$(vPrefix)
LOAD * FROM [ExcelFile](options);
LET vPrefix = 'CONCATENATE (Table1)';
:
NEXT
Best,
Peter
What is your folder structure and what you are going to get concatenate with your loaded script using For..Loop.
May be explain little more?
FOR EACH File in FileList ('Path\file *.xls')
test:
LOAD *
FROM
$(File)
next File;
I want generate a single table that's the concatenation of all files
Hi,
you can do this:
For each v_file in filelist (YOUR_PATH/FILE_NAME*.EXTENSION)
TABLE:
LOAD * (for example)
FROM '$(v_file)'
NEXT v_file;
so with an * on the filename you can load and concatenate your files, just know where exactly to put your *
The problem is, the tables arent exactly equal so he creates several tables and creates keys for the common fields. And i want concatenate all of them
No, you won't have to do this in a separate loop. You can concatenate those tables immediately, but you may end up with a data mess.
To solve the problem of QlikView deciding to create different tables anyway, because they look too different, add a prefix to the LOAD statement in your FOR EACH loop. If you store the prefix in a variable, you can modify it to be different for the first LOAD. Like in:
LET vPrefix = 'Table1:
NOCONCATENATE';
FOR EACH ...
:
$(vPrefix)
LOAD * FROM [ExcelFile](options);
LET vPrefix = 'CONCATENATE (Table1)';
:
NEXT
Best,
Peter
Hi Bruno,
In case the fields in the Excel files aren't exactly the same you could using te following script (based on youssefbelloum)
SET a = 0;
For each v_file in filelist (YOUR_PATH/FILE_NAME*.EXTENTION)
IF $(a) = 0 THEN
TABLE:
NOCONCATENATE LOAD * (for example)
FROM '$(v_file)'
ELSE
CONCATENATE (TABLE) LOAD * (for example)
FROM '$(v_file)'
END IF
LET a = $(a) + 1;
NEXT v_file;
My question was bit different, You already load some script which used for to get all excel files from path folder. Now what are you going to have with Concatenation like below
FOR EACH File in FileList ('Path\file *.xls')
test:
LOAD * FROM $(File);
next File;
Concatenate (test)
FOR EACH File in FileList ('Differen_Folder\filenames *.xls')
test:
LOAD * FROM $(File);
next File;
Perfect, thank you!!!
this do the job too. Thank you for all the help!