Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
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
Highlighted
Employee
Employee

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

Highlighted
Not applicable

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

Could I see your script?

Highlighted
Employee
Employee

An please a screenshot of your folder structure

Highlighted
Not applicable

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

Highlighted
Not applicable

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

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

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

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

Employee
Employee

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