Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What about =right(SubField('FMS_Consumo_201411.xls','.',-2),2)
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!
Could I see your script?
An please a screenshot of your folder structure
I would like, but there are too many scripts and I don't know which one to give you!
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 "-".
Are you passing a fixed string, right?
Please check that my formula returns last two characters from right before last "."
Thnx a lot but...from the screenshot I cannot see the entire formula!
Can you copy and paste it in a message, please?
=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)