topic if statement in QlikView

if statement

Tue, 29 Sep 2015 07:23:59 GMT

Hi all,

is it possible to use single IF statement for 2 Loads. I am facing the following issue.

I am using the following kind of statement. The problem here if iam using file1 twice to 2 different load statement. its is not loading the second one.

sub GetCSVFIleNames(Root)

for each FoundFile in filelist( Root & '\*.csv')

FoundFile = right('$(FoundFile)', 8);

let vTmpFoundFile=SubField(FoundFile,'\',-1);

//if filename contains file1

if Index('$(FoundFile)','file1-')>0 then

LOAD

[Customer Number],

[Customer Name],

[Product name] as [Product Name],

[Product number] as [Product Number]

FROM

['$(FoundFile)']

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

Elseif Index('$(FoundFile)','file2-')>0 then

Customer:

LOAD

[Customer Name],

[First Name],

[Address] as [Address]

FROM

['$(FoundFile)']

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

end if

next FoundFile;

for each SubDirectory in dirlist( Root & '\*' );

call GetCSVFIleNames(SubDirectory);

next SubDirectory;

end sub

for

Re: if statement

Tue, 29 Sep 2015 08:48:51 GMT

can anybody give some suggestions here. I need to call the same file in 2 load statements. The issue is

for ex: if iam calling file 1 ..its considers only ij load statement and other does not get loaded.

Re: if statement

Peter_Cammaert — Tue, 29 Sep 2015 09:20:56 GMT

I do not understand your question.

The script file loads only 'file1-*.csv' and ''file2-*.csv' from whatever content is available in the directory passed as a parameter. You will never load file1- ... twice.

There is a dangerous statement present in your code:

FoundFile = right('$(FoundFile)', 8);

This statement will

reset the loop variable to something else than the current value.
cut the filename short to a maximum length of 8 characters. Why? The next statement

let vTmpFoundFile=SubField(FoundFile,'\',-1);

will keep the entire file name, whatever its length and will drop the path anyway.

Omit that statement and try again.

Peter

Re: if statement

Tue, 29 Sep 2015 09:56:14 GMT

hi peter, this is the scenario

So I require file1 firist time to calculate and then again to load it. how can achieve this

im loading file1 first time to calculate certain

if(FieldNumber(indexrate', 'TEMP')>0) then
set rating ="[broarange)]";
set ratingfactor= 11;
end if

sub GetCSVFIleNames(Root)

for each FoundFile in filelist( Root & '\*.csv')

let vTmpFoundFile=SubField(FoundFile,'\',-1);

//if filename contains file1

f Index('$(FoundFile)','file1-')>0 then

temp:

LOAD *

from

['$(FoundFile)']

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

if(FieldNumber(indexrate', 'TEMP')>0) then
set rating ="[broarange)]";
set ratingfactor= 11;
end if

Elseif Index('$(FoundFile)','file1-')>0 then

Customer:

LOAD

Customer Name],

[First Name],

[Address] as [Address]

ratingfactor * ----- I will do certain calculations.

FROM

['$(FoundFile)']

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

end if

next FoundFile;

for each SubDirectory in dirlist( Root & '\*' );

call GetCSVFIleNames(SubDirectory);

next SubDirectory;

end sub

Re: if statement

Peter_Cammaert — Tue, 29 Sep 2015 12:10:52 GMT

[Edit] Sorry, the previous suggestion was simply stupid. I removed it.

The simplest fix is to omit the ELSIF line. The two LOADs will be perfomed in the same IF THEN body, since a single test for file1 will suffice. In order to avoid an Autoconcatenate, you may have to prefix the second load with the NOCONCATENATE keyword.

You are sure that file1-* will occur more than once in a specific directory? Otherwise it is of no use to call a SUB with an embedded FOR loop.

Peter

Re: if statement

Tue, 29 Sep 2015 12:40:49 GMT

hi peter,

are you saying to just maintain with if and end if for each load statement?

sub GetCSVFIleNames(Root)

for each FoundFile in filelist( Root & '\*.csv')

let vTmpFoundFile=SubField(FoundFile,'\',-1);

//if filename contains file1

if Index('$(FoundFile)','file1-')>0 then

temp:

LOAD *

from

['$(FoundFile)']

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

endif

if Index('$(FoundFile)','file1-')>0 then

Customer:

LOAD

Customer Name],

[First Name],

[Address] as [Address]

ratingfactor * ----- I will do certain calculations.

FROM

['$(FoundFile)']

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

end if

next FoundFile;

for each SubDirectory in dirlist( Root & '\*' );

call GetCSVFIleNames(SubDirectory);

next SubDirectory;

end sub

Re: if statement

Peter_Cammaert — Tue, 29 Sep 2015 13:16:39 GMT

That was my first suggestion, and I admit it was a pretty dumb one.

The revised one is better: just omit the ELSEIF line. No need to test twice for the same file. Put the two loads in the same IF index() > 0 THEN-body.

Or in your last example: delete the blue end if and the next if Index(...) > 0 then line.

Re: if statement

Tue, 29 Sep 2015 13:42:12 GMT

its perfect peter. I will do few more test by loading files and check