3 Replies Latest reply: Sep 27, 2017 1:22 PM by Elizabeth Oram RSS

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

    Elizabeth Oram



      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')

          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?

        • Re: Having trouble with variable & if..then in Data Load Editor
          Elizabeth Oram

          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



          Load *

          From [file location](qvd);


          Store Tablename into [archive file location](qvd)

          Drop tables ArchiveFiles, Tablename;




          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.

            • Re: Having trouble with variable & if..then in Data Load Editor
              Rob Wunderlich

              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.




                • Re: Having trouble with variable & if..then in Data Load Editor
                  Elizabeth Oram

                  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.