For Each concatenate

[brunopaulo]

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

[Anil_Babu_Samineni]

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?

[brunopaulo]

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

[YoussefBelloum]

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 *

[brunopaulo]

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

[Peter_Cammaert · Accepted solution]

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

[avkeep01]

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;

[Anil_Babu_Samineni]

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;

[brunopaulo]

Perfect, thank you!!!

[brunopaulo]

this do the job too. Thank you for all the help!