topic Re: Hi guys, Concatenation using loops in QlikView

Hi guys, Concatenation using loops

Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

i am using this script to concatenate files using loops, but with this loop there are many concatenations taking place and my data model doesn't look good...please help me how to avoid that using auto increment

//concate

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')

Concat:

LOAD

* from [$(vfile)]

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD

* from [$(vfile)]

(ooxml, embedded labels, table is Sheet1);

NEXT vfile

Thanks

Yogesh

Re: Hi guys, Concatenation using loops

swuehl — Fri, 25 Nov 2016 10:17:45 GMT

Could you detail what your issue is?

For example, how does your data model look like at the end and how do you want it to look like?

How are your input files named and is the structure of the input files the same?

Why do you load the input files twice?

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 10:23:28 GMT

Yea that's what it is loading twice and in my table viewer its showing like concat1,concat2. i want data to be loaded only once , cz i am new to qlikview i donno how to use auto increment ..so please help me how to do so.

my input files are named as emp1,emp2,emp3 etc.,

thanks Stefan for ua reply.

Re: Hi guys, Concatenation using loops

swuehl — Fri, 25 Nov 2016 10:25:53 GMT

What do you mean with auto increment? Which value do you want to increment?

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 10:28:37 GMT

auto increment in the sense every file should be loaded until it reaches last file.

Re: Hi guys, Concatenation using loops

Gysbert_Wassenaar — Fri, 25 Nov 2016 10:36:29 GMT

What is being incremented? And what makes it auto?

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 10:41:51 GMT

It should take every excel file one by one for that we need to use increment i guess.. i am not sure about it...

I will explain you complete scenario.

i have n number of excel files in a folder which will be updating every often...so instead of loading those each and every time i have used loops..but its loading twice..i want that to load only once so give some example script to get that fixed

Re: Hi guys, Concatenation using loops

Gysbert_Wassenaar — Fri, 25 Nov 2016 10:49:08 GMT

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')

Concat:

LOAD

* from [$(vfile)]

(ooxml, embedded labels, table is Sheet1);

Concatenate

LOAD

* from [$(vfile)]

(ooxml, embedded labels, table is Sheet1);

NEXT vfile

Inside the loop you execute the same load twice. You load the data twice from the same file. That's why you have double the data you want. Remove one of the two load blocks.

Re: Hi guys, Concatenation using loops

swuehl — Fri, 25 Nov 2016 10:50:46 GMT

The FOR EACH ... IN FILELIST() ...NEXT

should take care of iterating over all files in your folder, no need for additional actions

If you want to load the tables only once, then do so (remove the second load). Tables will be auto-concatenated if the structure (field names) is identical in all files:

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')

Table:

LOAD

* from [$(vfile)]

(ooxml, embedded labels, table is Sheet1);

NEXT vfile

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 10:56:47 GMT

If i remove any one of the block i wont get result. say if i remove first one script will fail and if i remove second one next file wont work..

Gysbert can u alter that script and reply me?

Thanks in advance

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 10:58:59 GMT

Stefan field names get change in every file...like one will have empno,deptno,sal and other file may contain empno,loc,Dept

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 11:03:12 GMT

after executing ua script i can see table-1 and table-2 in table table viewer.. which i dont want ...i need only one

Re: Hi guys, Concatenation using loops

Gysbert_Wassenaar — Fri, 25 Nov 2016 11:04:53 GMT

SET vConcatenate = ;

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')

Concat:

$(vConcatenate)

LOAD

FROM

[(vfile)] (ooxml, embedded labels, table is Sheet1)

;

SET vConcatenate = Concatenate;

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 11:05:01 GMT

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 11:10:27 GMT

Its working fine...thank you so much gysbert and stefan for ua assistance

Re: Hi guys, Concatenation using loops

Fri, 25 Nov 2016 11:17:41 GMT

i am not able to understand what you actually did ...can you tell me what you did and how it works?

Re: Hi guys, Concatenation using loops

MarcoWedel — Fri, 25 Nov 2016 21:54:25 GMT

Hi,

another method could be:

Concat:
LOAD 0 as TempField
AutoGenerate 0;

FOR Each vfile in FileList('C:\Users\w_318460\Desktop\New folder\*.xlsx')
  Concatenate
  LOAD * From [$(vfile)] (ooxml, embedded labels, table is Sheet1);
NEXT vfile

DROP Field TempField;

hope this helps

regards

Marco

Re: Hi guys, Concatenation using loops

Mon, 28 Nov 2016 05:08:18 GMT

Its working fine. thank you Macro

Re: Hi guys, Concatenation using loops

swuehl — Mon, 28 Nov 2016 09:40:04 GMT

If your request is resolved, then please close this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful