How to filter a .csv by the name

[#1]

Hi everybody,

I have a .qvw application with a couple of .csv as datasource, the file are named like  CMT_201406.csv  , CMT_201407.csv and there is an FTP that places the new file ( eg. CMT_201408.csv ) every month in the same folder. I want that the application catch the last file and not the other ones. ( every file contains not only the data of the month, but the entire dataset from january till the current month ).

In the script i have inserted the following line: right (FileBaseName(),02) as last2digit, in this way i have the last 2 digit of the file name, how can i set the load in order to catch the file in the folder with the last two digits higher?

Thanks in advance  

[tresesco]

Why don't you just overwrite the file? Anyway, the way you want could be achieved by something like:

Temp:

Load

     Max(right (FileBaseName(),6)) as MaxFile                        // Corrected

From < .../*.csv>;

Let vMax= Peek('MaxFile');

Let vFileName='CMT_'&$(vMax)&'.csv';                                 // Corrected

Load

          *

From <..../&'$(vFileName)' >;

[Peter_Cammaert]

If you want this code to cross year ends, change the 02 parameter in Tresesco's first LOAD into a 06. In that way, the script'll grab CMT_201501.csv instead of CMT_201412.csv

[tresesco]

Good catch. For mutiple year data maintenance, all six digits have to be considered.

[tabletuner]

Set vFileName='CMT_'&$(vMax)&'.csv';

should be

LET vFileName='CMT_'&$(vMax)&'.csv';

Regards,

Tjeerd

[Peter_Cammaert]

SET vFileName = 'CMT_$(vMax).csv';

will also work. String substitution will always be applied, whatever the statement.

[#7]

I've tried with this script:

Temp:

Load

     Max(right (FileBaseName(),2)) as MaxFile

From *.csv;

Let vMax= Peek('MaxFile');

set vFileName=CMT_'&'$(vMax)'.csv ;

Load  *

FROM <$(vFileName)>;

but it did not caught anything from the file.

i've added a text box to show the value of the variable vFileName and this was the result:

I cannot understand where the error could be, surely is in the definition of the variable vFileName.

[Peter_Cammaert · Accepted solution]

Use this script. It's an assembly of various comments from above:

Temp:

LOAD Max(Right(FileBaseName(), 6)) AS MaxFile

FROM '*.csv' (txt);

LET vMax = Peek('MaxFile');

DROP Table Temp;

Data:

LOAD *

FROM 'CMT_$(vMax).csv' (txt);

If you want to know the value of vMax, just open Settings->Variable Overview... at the end of the script run, and scroll down until you encounter vMax in the variable list.

[#9]

It works.

thanks everybody for the help.

M