Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Get file name in a textbox

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

13 Replies
Employee
Employee

Re: Get file name in a textbox

What about =right(SubField('FMS_Consumo_201411.xls','.',-2),2)

Highlighted
Not applicable

Re: Get file name in a textbox

Hi Clever Anjos,

Unfortunately it doesn't work!

What I forgot to write is the following: the file contained in that directory will change each month with a file whose file name contains the month.

I would like to apply a formula so that, each time I change the file with "FMS_Consumo_201412.xls" and then with "FMS_Consumo_201501.xls" and so on, the textbox automatically updates once I reload new file and data.

Anyway thaks for the quick reply!

Highlighted
Employee
Employee

Re: Get file name in a textbox

Could I see your script?

Highlighted
Employee
Employee

Re: Get file name in a textbox

An please a screenshot of your folder structure

Highlighted
Not applicable

Re: Get file name in a textbox

I would like, but there are too many scripts and I don't know which one to give you!

Highlighted
Not applicable

Re: Get file name in a textbox

How can I give you a screenshot of the folder structure? I don't jnow how and i thought the path was enough!

Anyway, the code i wrote lastly is the following:

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

But the result of the formula is "xls".

If I writ "-1", instead, the result is "-".

Highlighted
Employee
Employee

Re: Get file name in a textbox

Are you passing a fixed string, right?

Please check that my formula returns last two characters from right before last "."

Capturar.PNG

Highlighted
Not applicable

Re: Get file name in a textbox

Thnx a lot but...from the screenshot I cannot see the entire formula!

Can you copy and paste it in a message, please?

Highlighted
Employee
Employee

Re: Get file name in a textbox

=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)