topic Re: Re: Compare FileName with a field from a txt in QlikView

Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 09:43:20 GMT

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

Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 10:14:23 GMT

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

Re: Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 10:28:09 GMT

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

Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 10:37:09 GMT

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.

Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 10:47:30 GMT

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

Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 11:07:50 GMT

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

Re: Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 12:21:41 GMT

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.

Re: Compare FileName with a field from a txt

Anonymous — Tue, 21 Oct 2014 12:25:54 GMT

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.