topic Re: Scripting Issue in QlikView

Scripting Issue

Wed, 15 Oct 2014 12:07:58 GMT

I have a folder which conatins many csv files with names such as aaa MAY 2014, aaa Jun 2014 and so on.

How do i load only those csv files which are received after MAY 2014.

Re: Scripting Issue

Wed, 15 Oct 2014 12:15:21 GMT

If the file name contains the month name, you can use the function FilebaseName() to get the name of the file and filter.

Re: Scripting Issue

johanlindell — Wed, 15 Oct 2014 12:32:40 GMT

Convert the part of the FilebaseName() with the "date" in it into a date and the check this date if you should proceed:

LET fileDate = Date(Date#('01 MAY 2014', 'DD MMM YYYY'), 'YYYY-MM-DD')

This will return a date for that you can compare with in an IF statement.

Re: Scripting Issue

Wed, 15 Oct 2014 12:53:22 GMT

I have a requirement of loading a particular field from the csv file only after June 2014. For the previous years that field is not present in the file.

I have used this expressoin

if(date(date#(textbetween(lower(Filename()),'data', '.csv'),' MMM YYYY'),'MMM YYYY')>'May 2014', FieldName,'') as FieldName

the above expression throws up an error Field not found.

Re: Scripting Issue

Anonymous — Wed, 15 Oct 2014 13:44:57 GMT

Hi,

this won't work in your loadstatementn because QlikView will interprete FieldName as if there is a field with that name resulting in Error if not. There are a several ways to do that.

Depending on your script you could try (if you are looping through all files with filelist() for example) a control statement, using different loads:

if date(date#(textbetween(lower(subfield('$(vFile)', '\', (substringcount('$(vFile)', '\')+1))),'data', '.csv'),' MMM YYYY'),'MMM YYYY')> date(MakeDate(2014, 5), 'MMM YYYY') then

load

FieldName

From $(vFile);

else

load

From $(vFile);

endif

Hope this helps.

Best regards

Stefan