topic Re: How to filter a .csv by the name in QlikView

How to filter a .csv by the name

Tue, 21 Oct 2014 09:59:28 GMT

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

Re: How to filter a .csv by the name

tresB — Tue, 21 Oct 2014 10:09:13 GMT

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

Re: How to filter a .csv by the name

Peter_Cammaert — Tue, 21 Oct 2014 11:03:50 GMT

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

Re: How to filter a .csv by the name

tresB — Tue, 21 Oct 2014 11:10:57 GMT

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

Re: How to filter a .csv by the name

tabletuner — Tue, 21 Oct 2014 11:52:18 GMT

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

should be

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

Regards,

Tjeerd

Re: How to filter a .csv by the name

Peter_Cammaert — Tue, 21 Oct 2014 12:12:30 GMT

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

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

Re: How to filter a .csv by the name

Tue, 21 Oct 2014 22:04:48 GMT

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.

Re: Re: How to filter a .csv by the name

Peter_Cammaert — Tue, 21 Oct 2014 22:24:16 GMT

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.

Re: Re: How to filter a .csv by the name

Wed, 22 Oct 2014 12:25:29 GMT

It works.

thanks everybody for the help.