topic Re: For each in Filelist - only selects the first file in QlikView

For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 01:33:21 GMT

Hello Guys,

I appreciate if you help me solve this issue,

I am trying to fetch a list of qvd file names, then based on the date (YYYY-MMM) in their names, only select the last one (the most recent one)

Table names are like:

MyTable_2017-Jan

MyTable_2016-Dec

,...

So far I'm here:

for each vFile in FileList('lib://QlikData/*.qvd')

Files:

LOAD *, FileBaseName() as FileName

From $(vFile);

Let vMaxFile=Max(Date((SubField(FileName,'_',-1)),'YYYY-MMM'));

Load * from ['lib://QlikData/TsbleName_$(vMaxFile).qvd'](qvd);

I am expecting this to load the last qvd, but it doesn't

Re: For each in Filelist - only selects the first file

Anil_Babu_Samineni — Tue, 06 Jun 2017 02:07:46 GMT

May be use

for each File in filelist ('lib://QlikData/*.qvd')

Files:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Files

Order By FileTime DESC;

drop table Filed;

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 02:40:40 GMT

Thanks Anil,

But I really need to use the date in the fileName. The modified date of these files are not reliable and I need to use that date later in my code.

Isn't there any way to resolve that ?

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 03:40:50 GMT

Just to test, I used your script as below :

for each vFile in FileList('lib://QlikData/*.qvd')

Files:

Load '$(vFile)' as FName,

FileTime( '$(vFile)' ) as FileTime

autogenerate 1;

next vFile

LatestFile:

first 1

Load

FName,

FileTime,

1 as dummy

Resident Files

Order By FileTime DESC;

drop table Files;

Let vMaxFile=FName;

Consignments:

Load * from [lib://QlikData/'$(vMaxFile)'](qvd);

It doesn't load any file, the error is :

The following error occurred:

Cannot open file: 'lib://QlikData/'''

Re: For each in Filelist - only selects the first file

Anil_Babu_Samineni — Tue, 06 Jun 2017 04:23:59 GMT

It's clearly telling related your files are not located in QlikData folder. Can you cross check that and connect manually those filed by help of guide then have a look and consider my script to get latest one

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 04:43:57 GMT

Anli,

I checked the logs, it reads all the files and pick the maximum, but on below step, it fails to load it with a variable

I think the issue is there..do you have any suggestion?

Load * from [lib://QlikData/$(vMaxFile)](qvd);

Re: For each in Filelist - only selects the first file

Anil_Babu_Samineni — Tue, 06 Jun 2017 04:52:31 GMT

May be use NoConcatenate on your last statement like below

Consignments:

NoConcatenate

Load * from [lib://QlikData/'$(vMaxFile)'](qvd);

remove the single colon on your variable while loads statement like below

Load * from [lib://QlikData/$(vMaxFile)](qvd);

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 04:56:39 GMT

So you think, normally it's ok to use variables inside the lib address?

This is the first time that I want to load that qvd file and using NoConcatenate doesn't change anything, I tried

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 05:11:46 GMT

I guess I realized what the issue is,

when using for each vFile, it actually stores the whole link into this variable.

For instance, vFile becomes lib://QlikData/MyTable_2016-Aug.qvd

But it should only contain "MyTable_2016-Aug.qvd"

do you have any suggestion?

Re: For each in Filelist - only selects the first file

jonathandienst — Tue, 06 Jun 2017 05:20:11 GMT

If you want to select the file based on the year/month in the filename, then you can do this:

vMaxDate = 0;

vFinalFile ='';

For Each vFile in FileList('lib://QlikData/*.qvd')

vDate = Date#(TextBetween(vFile, 'Table_', '.qvd'), 'yyyy-MMM');

vFinalFile = If(vDate > vMaxDate, vFile, vFinalFile);

vMaxDate = RangeMax(vMaxDate, vDate);

Next

Consignments:

Load * from [$(vFinalFile)] (qvd);

Set vMaxDate =;

Set vFile =;

Set vFinalFile =;

This is based on the details in your original post.

Re: For each in Filelist - only selects the first file

arixooo123 — Tue, 06 Jun 2017 05:58:55 GMT

Thanks, Jon

I think the issue must be clarified, I have a couple of qvd files in a folder with the structure that I already mentioned:

MyTable_2017-Jan.qvd

MyTable_2016-Dec.qvd

, ....

I want to dynamically select the last qvd file. Please let me know if I could give any more information

What I don't understand in your script is that whether it needs me to give some MaxDate or FinalFile ? because there is no maxDate or FinalFile...the files are automatically created and are supposed to be continued forever

Re: For each in Filelist - only selects the first file

Anil_Babu_Samineni — Tue, 06 Jun 2017 06:10:38 GMT

That's true, It will store as full link only. And, I would read few articles when you need separate file names you must include by help of SubField() to fetch the data initially then we can fetch max / latest number.

May be helps you

Re: For each in Filelist - only selects the first file

jonathandienst — Tue, 06 Jun 2017 06:12:25 GMT

>>I want to dynamically select the last qvd file.

And that is precisely what this script does.

>>What I don't understand in your script is that whether it needs me to give some MaxDate or FinalFile.

Uhm, no. The script is self-contained. The only thing required is the file path in the FileList()

Re: For each in Filelist - only selects the first file

arixooo123 — Thu, 08 Jun 2017 01:02:04 GMT

Hi Jonathan,

I am deeply grateful for your help

Would you please also check my script on below link?

Incremental Loading on monthly Partitioned QVDs

Re: For each in Filelist - only selects the first file

arixooo123 — Fri, 09 Jun 2017 04:08:37 GMT

Hi Jonathan,

Sorry to interrupt you, but I've been trying to fix this script for 3 days and no success yet. I'll appreciate if you could take a look at it and let me know what is wrong with it.

---------------------------------------------------------------------------------------------------------------------------------------

// Selecting the last qvd file (this part works like a charm)

vMaxDate = 0;

vFinalFile ='';

For Each vFile in FileList('lib://QlikData/Consigments/*.QVD')

vDate = Date#(TextBetween(vFile, 'Consigments_', '.QVD'), 'yyyy-MMM');

vFinalFile = If(vDate > vMaxDate, vFile, vFinalFile);

vMaxDate = RangeMax(vMaxDate, vDate);

Consignments:

Load * from [$(vFinalFile)] (qvd);

Set vMaxDate =;

Set vFile =;

Set vFinalFile =;

//Finding the last updated date

ConsDate:

Load max(Created) as MaxDate

resident Consignments;

Let vCDate=Date(Peek('MaxDate',0,'ConsDate'),'YYYY-MM-DD');

Drop table Consignments;

LIB CONNECT TO 'DB';

IncrementalCons:

Load *;

SQL

select * from

DB.dbo.Consignments where Created>'$(vCDate)' or Edited>'$(vCDate)';

YearData:

LOAD

DISTINCT Date(Created,'YYYY-MMM') AS YrMonth//(Year(Created)&'-'& Month(Created)) AS YrMonth // Formatting the Date

Resident IncrementalCons;

FOR i = 0 to NoOfRows('YearData')-1;

LET vYrMonth = Peek('YrMonth',$(i),'YearData');

FinalTable:

LOAD *

Resident IncrementalCons

WHERE Date(Created,'YYYY-MMM')='$(vYrMonth)';// (Year(Created)&'-'& Month(Created)) = '$(vYrMonth)';

//If the qvd already exists, it should be updated, but if it's a new month data we should create a new qvd file

If ($(vYrMonth)<=$(vMaxDate)) then

Concatenate

Load * from [$(vFinalFile)](qvd) where not Exists(Id);

STORE FinalTable into '$(vPathStore)/Consigments/Consigments_$(vYrMonth).QVD'(QVD);

else

STORE FinalTable into '$(vPathStore)/Consigments/Consigments_$(vYrMonth).QVD'(QVD);

end if;

DROP Table FinalTable;

next i;

drop Table IncrementalCons,YearData,ConsDate;

Re: For each in Filelist - only selects the first file

jonathandienst — Mon, 12 Jun 2017 05:46:46 GMT

It would be easier to help you if you described in detail what is wrong. I see no major issues by eyeballing it. Are you getting an error? if so, what? Or are you getting incorrect results?

I suggest that you start a new thread for this as the original question in the thread has been answered.

Re: For each in Filelist - only selects the first file

arixooo123 — Mon, 12 Jun 2017 06:35:15 GMT

Hi Jon,

I really appreciate your help

I already created another thread for this: the problem is in writing the slice of data in its related QVD files. The part I wrote down an IF. The selected data could be related to different QVDs (Since data in last months could be edited) but we store it in QVD based on Created data. So, imagine a scenario in which the selected data in the last window is related to the last 3 months QVDs. We should update the first 2 QVDs and create another QVD file for the current month (let's assume the month just changed and now we have a new month)

Incremental Load on monthly Partitioned QVDs

-----------------------------------------------------------------------------------------------------------

Let me give you an example:

Id Fact Columns Edited Created

100 xxxxxxxxxxxx 2017-06-01 2017-05-05

200 xxxxxxxxxxxx 2017-06-01 2017-04-05

300 xxxxxxxxxxxx 2017-06-01 2017-03-05

400 xxxxxxxxxxxx - 2017-06-01

The first 3 rows (100,200,300) must be updated into their related QVD files, but the last row( 400) should be added to a new qvd file for month of June. (assuming this is the first time we do incremental loading in June and there is no qvd file created for this month before)