Get file name in a textbox

[#1]

Hello everyone,

I'm new to Qlikview and this is my first post here in the community.

My issue is the following.

I need to create a textbox in which I can write "Updated in MONTH and YEAR", where I know how to get YEAR in QlikView but don't know how to get MONTH.

Given the structure of directories (see the formula below) and the name of a file which contains the MONTH, I would like to get the MONTH from the file name.

The file name is "FMS_Consumo_201411.xls".

What I tried was something like that: create the textbox and try with SubField and SubStringCount to see the result and then apply Right/Left/Mid to SubField result. The formula used is the following:

=SubField('C:\Users\vmosca\Desktop\CRUSCOTTO ICT GENERALI - ECONOMICS DC E FC_2\DATI\FILE INPUT\FMS_CONSUMO\FMS_CONSUMO','\',SubStringCount('C:\Users\vmosca\Desktop\CRUSCOTTO ICT GENERALI - ECONOMICS DC E FC_2\DATI\FILE INPUT\FMS_CONSUMO\FMS_CONSUMO','\')+1)

The result is "FMS_CONSUMO" and not "FMS_Consumo_201411.xls".

Does a way exist to sort out this issue?

Both suggestions on the solutions I tried or other ways the resolve are well accepted.

Thanks a lot for seeing this post and thank in advance to all who reply.

Regards,

Vincenzo Mosca

[#2]

Yes it is!! (check that next to xls there are 2 ''):

The code with ERRATA CORRIGE is:

=right(SubField('C:\Users\vmosca\Desktop\CRUSCOTTO ICT GENERALI - ECONOMICS DC E FC_2\DATI\FILE INPUT\FMS_CONSUMO\FMS_CONSUMO\FMS_Consumo_20****.xls','.',-2),2)

So, the formula is right! Thnx a lot!

But still I have a problem: I used **** because I thought the software would have given numbers/letters instead of ****.

How can I get MONTH automatically, without any manual changes in textbox code when I do updates?

[Clever_Anjos]

Since you´re hardcoding your filename is tough to calculate this,

Why don´t you use FileBaseName() while reading your excel file? This would populate a field into your data, so it would be automatic to get that information

[MarcoWedel]

LOAD somefields,
          Month(Date#(Right(FileBaseName(),2),'MM')) as Month
FROM path\FMS_Consumo_201412.xls 
...

should deliver 'Dec'.

hope this helps

regards

Marco

[#5]

Hi Marco,

The option you gave me wasn't so helpful, unfortunately!

I also thought another way.

Given a column in the xls file than I want to load, the data in the column is the same for each 4k and more rows and is "CNSM11". This number will change any time an update is available.

So, I thought to load a specific cell (e.g. A2) from the xls file and apply the Right formula to get the last 2 numbers of the value in the cell.

Do you think this is a possible solutions?

Can you give me a hint?

Thanks in advance!