For each in Filelist - only selects the first file

[arixooo123]

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

[arixooo123]

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

[Anil_Babu_Samineni]

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

[jonathandienst]

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

[arixooo123]

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

[arixooo123]

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

Next

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;

[jonathandienst]

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.

[arixooo123]

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)