topic Re: For Each concatenate in QlikView

For Each concatenate

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: For Each concatenate

Anil_Babu_Samineni — Tue, 28 Nov 2017 09:36:54 GMT

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?

Re: For Each concatenate

brunopaulo — Tue, 28 Nov 2017 09:40:30 GMT

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

Re: For Each concatenate

YoussefBelloum — Tue, 28 Nov 2017 09:40:56 GMT

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 *

Re: For Each concatenate

brunopaulo — Tue, 28 Nov 2017 09:42:57 GMT

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

Re: For Each concatenate

Peter_Cammaert — Tue, 28 Nov 2017 09:43:24 GMT

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)';

Best,

Peter

Re: For Each concatenate

avkeep01 — Tue, 28 Nov 2017 09:44:03 GMT

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;

Re: For Each concatenate

Anil_Babu_Samineni — Tue, 28 Nov 2017 09:44:16 GMT

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;

Re: For Each concatenate

brunopaulo — Tue, 28 Nov 2017 09:47:37 GMT

Perfect, thank you!!!

Re: For Each concatenate

brunopaulo — Tue, 28 Nov 2017 09:48:29 GMT

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