topic Assign Excel file name to a variable in QlikView

Assign Excel file name to a variable

divya_anand — Fri, 03 Nov 2017 06:30:34 GMT

Hi,

I have excel files with the name that include the date. I would like to extract this date in the file name and assign it to a variable.

Any thoughts how I could achieve this?

I use the below expression to extract the date

Date(Date#(SubField(FileBaseName(),'-',1),'YYYYMMDD'),'DD-MMM-YYYY')

Thank you.

Anonymous — Fri, 03 Nov 2017 06:35:41 GMT

Can you provide the sample Filename??

Anonymous — Fri, 03 Nov 2017 06:47:03 GMT

Hi divya,

Your expression looks fine. To assign a value to a variable you can use Peek() after the load statement.

Anil_Babu_Samineni — Fri, 03 Nov 2017 07:08:06 GMT

Try to split expression and check then date and date#

Anonymous — Fri, 03 Nov 2017 08:10:03 GMT

you cant assign variable properly in a load statement.

can you do the assignment of variable and load statement separately?

Ralf_Heukäufer — Fri, 03 Nov 2017 09:44:39 GMT

Hello,

is it one file or more?

If it is one File and the data is in your table you can try something like this:

Date(Date#(SubField(FileBaseName(),'-',1),'YYYYMMDD'),'DD-MMM-YYYY') as Filename

Temp_Filename:

Load Max(Filename) as tempMax Resident NameofyourTable;

let v_Filename = FieldValue ('tempMax',1);

Drop Table Temp_Filename;

In the case you have more than one filenames you must work with a for each loop and a variable instead of the filename.