Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
brunopaulo
Partner - Creator II
Partner - Creator II

For Each concatenate

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

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
brunopaulo
Partner - Creator II
Partner - Creator II
Author

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
Champion
Champion

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Champion III
Partner - Champion III

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
Partner - Specialist
Partner - Specialist

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
brunopaulo
Partner - Creator II
Partner - Creator II
Author

Perfect, thank you!!!

brunopaulo
Partner - Creator II
Partner - Creator II
Author

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