Having trouble with variable & if..then in Data Load Editor

[#1]

Hello,

I'm trying to create a script that first loops through & loads the FileBaseName() of all files ending with .qvd in a folder, then, if a certain filename doesn't exist, create that file by loading another file and saving it to that filename.

The first part looks like this, and works like it's supposed to, i.e. it loads a table with each filename.

Directory [$(vSource)/QVDs & CSVs/Archive QVDs];

For Each File in FileList('*.qvd')

ArchiveFiles:
    Load Distinct
     FileBaseName() as Archive
    From [$(File)](qvd);
   
Next File;

Then, I want to check if a certain filename exists in the field Archive. If it doesn't, I want to create the file and then drop the table ArchiveFiles, and if it does, I just want to drop the table ArchiveFiles. I've tried creating a variable that will be True or 1 or Y if the filename exists in the Archive field, and False or 0 or N if it doesn't, and then using the value of that variable in an If..then..else..end if statement. I haven't been able to get it to work. I've also tried directly using the Match() function in the if statement, and that hasn't worked either. Can anyone give some advice on this?

[#2]

To clarify, I have been able to get a variable that returns the correct answer to whether the filename exists in the Archive field or not. The problem occurs with the following If..then..else..end if part. So far I've been using this structure:

If vArchive='False' then

Tablename:

Load *

From [file location](qvd);

Store Tablename into [archive file location](qvd)

Drop tables ArchiveFiles, Tablename;

Else

Drop table ArchiveFiles;

End if;

What's happening is that the value of vArchive is equal to True, but the if statement is acting as if it's False.

[rwunderlich]

Your variable ref should be quoted and use DSE like this:

If '$(vArchive)'='False' then

When debugging script statements that use variables, look in the script document log to see what the statement looks like after variable substitution.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#4]

Thank you, I think that will help. Unfortunately I think I either misidentified the problem or changed something in the variable creation that broke it, because as of right now I can't get the variable to give me the correct value.

I've tried quite a few different ways to create the variable, using the Match() and WildMatch() and Peek() functions, and none of them seem to be working. I want it to return True if a value exists in the Archive field, or False if it doesn't. Here are a couple examples of ways I've tried to do it:

Let vArchive = If(Match('CostArchive', Peek('Archive')), True(), False());

Let vArchive = If(Match(Archive, 'CostArchive'), True(), False());

Let vArchive = If(Peek('Archive')='CostArchive', True(), False());

I've also tried it using 1 and 0 instead of true and false. The variable always returns False, even though the value 'CostArchive' exists within the field Archive. Is there some better way of checking to see if a value exists in a field? I've also tried using Count(If()) and Exists(), but that hasn't worked either.

[balabhaskarqlik]

Did you resolved the issue, post the solution.

[rwunderlich]

Hi Bala,

I notice you frequently post "Did you resolved the issue, post the solution." on old threads causing these threads to become active again.  I think it's best to post only if you have something to add to the thread, otherwise it generates noise for those folks who are trying to monitor and assist in active threads.

This is just my opinion, I'm not a moderator.

Thanks for your other contributions to the Forums BTW.

-Rob