topic Re: Load multiple files with for each in QlikView

Load multiple files with for each

Thu, 27 Jun 2013 09:50:59 GMT

I am trying to get this script wordking, but apparently I am missing something as it is not working...

I have multiple 'DES' files that need to be loaded in a loop to get 1 complete file.

Let

path_DESADV= G:\Hollander\Productie\Data\ExternalData\desadv\level_4\;

For Each File in FileList(DESADV)

DESADVFILES:

LOAD @1,

if(IsNull(@1),0,1) as Regel,

if(@1='RFFON', Left(@2,1)) as Inkoop,

if(@1='RFFON', Right(@2,7)) as Relatie,

     if(@1='RFFON', Mid(@2,2,5)) as Inkoopnr,

if(@1='NADSU', @2) as GNLnr,

     @2,

     If(left(@1,6)='DTM137',right(@1,4)) as Tijd,

     If(left(@1,6)='DTM137',Mid(@1,7,8)) as Datum,

filebasename() as FILE

FROM

G:\Hollander\Productie\Data\ExternalData\desadv\level_4\*.DES

(txt, codepage is 1252, no labels, delimiter is ' ', msq);

NEXT File

Re: Load multiple files with for each

Thu, 27 Jun 2013 09:59:50 GMT

DESADVFILES:
LOAD @1,

  if(IsNull(@1),0,1) as Regel,

  if(@1='RFFON', Left(@2,1)) as Inkoop,

  if(@1='RFFON', Right(@2,7)) as Relatie,

     if(@1='RFFON', Mid(@2,2,5)) as Inkoopnr,

  if(@1='NADSU', @2) as GNLnr,

     @2,

     If(left(@1,6)='DTM137',right(@1,4)) as Tijd,

     If(left(@1,6)='DTM137',Mid(@1,7,8)) as Datum,

  filebasename() as FILE



FROM

G:\Hollander\Productie\Data\ExternalData\desadv\level_4\*.DES

(txt, codepage is 1252, no labels, delimiter is ' ', msq);

This code is used to read the all files(DES extension file becaue we have given a * symbol in location G:\Hollander\Productie\Data\ExternalData\desadv\level_4\*.DES) from ur loaction then why do need for each loop.

If u not satatisfy with this answer, Pls explain ur issue breifly.

Regards,

Kabilan K.

Re: Load multiple files with for each

Thu, 27 Jun 2013 10:06:02 GMT

FILE	Relatie	GNLnr
I0266471	-	8.712.800.000.006
I0266471	-	-
I0269841	66UN515	-
I0269841	-	8.710.506.000.016
I0269841	-	-
I0269842	66UN515	-
I0269842	-	8.710.506.000.016
I0269842	-	-

Because I want to have the data in the samen 'line'. Key is the file name. If just load all files, I get the following:

Re: Load multiple files with for each

Thu, 27 Jun 2013 10:17:01 GMT

SET vRawFile= 'FILE1', 'FILE2','FILE3';

Execute cmd /c mkdir ..\ RawFileQVDs\;

SET vDirRawFile = ..\ RawFileQVDs\;

For each vTab in $(vRawFile)


     SET vDomain = 'ALLFile';
     Endif

     $(vTab):
     Load *;

     Select * from /$(vDomain)/$(vTab);
     Store $(vTab) into $(vDirRawFile)$(vTab).qvd;

Next

vTab;

Hope it will help u..

Regards-Bika

Re: Load multiple files with for each

Thu, 27 Jun 2013 10:23:09 GMT

Thanks. I will try it. One q: with FILE1 etc, you mean I need to indicate the seperate files? It is over 100+ files and changing every day...