loading different field names for different file names in loop

Tue, 05 Jan 2016 11:26:58 GMT

Hello everybody,

I would like to start off by wishing you all a happy new year!

My first question of 2016 is about looping using if conditions.

I have multiple csv files from which I would like to load different field names, depending on which csv.

I have written a loop that can load all fields from all csv files by simply using making a table that includes all filenames in the folder and the header sizes per csv file.

for i= 0 to noofrows('Mwoah')-1

LET myfilename = peek('SourceFile',$(i),'table1');

LET headersize = peek('Headersize',$(i),'table1');

load *

from [$(myfilename)]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

NEXT

However, I would like to amend this script so that if the filename contains a certain text string, it should load specific fields.

I want to use an if...then clausule for this.

I was thinking about using this:

for i= 0 to noofrows('table1')-1

LET myfilename = peek('SourceFile',$(i),'table1');

LET headersize = peek('Headersize',$(i),'table1');

IF substringcount($(myfilename),'TRD_ETD_VAL')>0 then

nieuwetabel:

load filename() as Filename,

[Party 1 Transaction ID] as [Transaction ID],

[Message Type],

[Transaction Type],

Action,

[UTI Prefix (Pos)],

[UTI Value (Pos)] as UTI

from [$(myfilename)]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

elseif substringcount($(myfilename),'TRD_ETD_TX')>0 then

load filename() as Filename,

[Party 1 Transaction ID] as [Transaction ID],

[Message Type],

[Transaction Type],

[Action],

[UTI Prefix (TX)] as [UTI Prefix],

[UTI Value (TX)] as UTI

from [$(myfilename)]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is $(headersize) lines);

endif;

NEXT

When I load this script I get the error message that the line elseif substringcount($(myfilename),'TRD_ETD_TX')>0 then is false. What do I need to do to get this script working? Note that the loop should check whether the filename contains a certain string, and if it does it should load the appropriate file names, if it does not contain the string it should proceed to test whether the filename contains another text string and if it does it should load appropriate file names, if it does not, it should proceed to the next condition and so on, and so forth.

Re: loading different field names for different file names in loop

MarcoWedel — Tue, 05 Jan 2016 22:47:25 GMT

Hi,

maybe using single quotes:

elseif substringcount('$(myfilename)','TRD_ETD_TX')>0 then

hope this helps

regards

Marco

topic loading different field names for different file names in loop in QlikView

loading different field names for different file names in loop

Re: loading different field names for different file names in loop