topic Re: Get file name in a textbox in QlikView

Get file name in a textbox

Fri, 16 Jan 2015 15:26:27 GMT

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

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 15:30:16 GMT

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

Re: Get file name in a textbox

Fri, 16 Jan 2015 15:38:50 GMT

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!

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 15:52:31 GMT

Could I see your script?

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 15:56:40 GMT

An please a screenshot of your folder structure

Re: Get file name in a textbox

Fri, 16 Jan 2015 15:59:36 GMT

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

Re: Get file name in a textbox

Fri, 16 Jan 2015 16:04:59 GMT

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 "-".

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 16:19:14 GMT

Are you passing a fixed string, right?

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

Re: Get file name in a textbox

Fri, 16 Jan 2015 16:28:28 GMT

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

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

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 16:45:04 GMT

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

Re: Get file name in a textbox

Fri, 16 Jan 2015 16:53:53 GMT

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?

Re: Get file name in a textbox

Clever_Anjos — Fri, 16 Jan 2015 17:07:33 GMT

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

Re: Get file name in a textbox

MarcoWedel — Sat, 17 Jan 2015 00:18:01 GMT

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

should deliver 'Dec'.

hope this helps

regards

Marco

Re: Get file name in a textbox

Tue, 20 Jan 2015 08:25:38 GMT

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!