Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
pitutiti
Contributor III
Contributor III

Compare FileName with a field from a txt

Hi everyone, maybe somebody can show me the light

My application takes the data from a lot of excel files. To do that I use a for each loop.

//Here I load the files that had been already loaded

PreviousFilesInside:

  LOAD PreviousFileName

  FROM FilesInside.txt

  (txt, utf8, embedded labels, delimiter is '\t');

           for each FileExtension in 'xls'

                     //Here I load every file in the folder

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

                      let temp=subfield('$(FoundFile)', '\' ,-1);

                      let fileName=SubField('$(temp)','.', 1); 

                 //This table stores the files which have been already loaded

                 FilesInside:

                 Load

                 filename

                 Inline

                 [filename

                 '$(fileName)'];

                     //Here if the file found is not in the PreviouFileName field

                    // will be loaded

                  if(Exists(PreviousFileName,'$(filename)')<>-1)

                    ...Loading data

                  end if

           next FoundFile

          next FileExtension

     //Here we save the new files in the txt file

     STORE filename AS PreviousFileName FROM FilesInside Into FilesInside.txt (txt);

The problem is that I can´t compare properly the two filesnames. This statement doesn't work

if(Exists(PreviousFileName,'$(filename)')<>-1)

Does anybody know what am I doinq wrong?

Thanks

7 Replies
karthigayan
Creator II
Creator II

As per the script, I think the file name is not going and saved in the PreviousFilename.

change the script as below and check if its working

STORE $(filename) AS PreviousFileName FROM FilesInside Into FilesInside.txt (txt);

pitutiti
Contributor III
Contributor III
Author

Hi, Kartthinagan, It does indeed.See the attached file. What I want is to search into this PreviousFileName and see if every file that I'm currently founding in the data folder, already exists in that field.

Thanks

karthigayan
Creator II
Creator II

I dont understand y u always reading the data from the txt file and also writing back to that file with the new value.

Instead, U can load the data from that txt file once and store it into a qvd.

and then compare the new filename with the values in the Qvd file

and in the end u can just concatenate the newdata into the table and store it into the same QVD file again.

I think this will work fine.

pitutiti
Contributor III
Contributor III
Author

Surely you're right but I think my problem is not where I store the data (I'll try it your way) but how to compare the two values in the IF statement.

Thanks a lot for your time

karthigayan
Creator II
Creator II

See if u follow my method u can do the comparison like this.

TempCount:

Load count(PreviousFileName) as count

from

QVDfile.qvd(qvd)

where PreviousFileName = '$(filename)';


//if there is a filename that exists in the qvd, then there will be one row otherwise there will be no rows


let vCount = Peek('count');


//now in the script u can write as


if('$(vCount )'<>-1)

//script continues

pitutiti
Contributor III
Contributor III
Author

Finally I've achived what I wanted this way

if len(lookup('PreviousFileName', 'PreviousFileName', '$(fileName)', 'PreviousFilesInside'))=0 THEN

End if

I'd tried your method and I like it too. I've made note. What I don´t appreciate is if is really there a big difference between storing the filenames  in a txt file or in a qvd.

Thanks again.

karthigayan
Creator II
Creator II

Good to hear that u have achieved the solution.

Ofcourse there are lot of differences when u use QVD and Txt file.QVD file is an optimized one.

and also using lookup will increase the reload time.