Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)' >;
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
Good catch. For mutiple year data maintenance, all six digits have to be considered.
Set vFileName='CMT_'&$(vMax)&'.csv';
should be
LET vFileName='CMT_'&$(vMax)&'.csv';
Regards,
Tjeerd
SET vFileName = 'CMT_$(vMax).csv';
will also work. String substitution will always be applied, whatever the statement.
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.
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.
It works.
thanks everybody for the help.
M