topic Re: Trim Field Name in QlikView

Trim Field Name

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

I have a data set that I am unable to link up to a date apart from with the file name. The file name in this case is named SHIP_Night_Week2.xls for each week etc. I need to change it to just 2 so need to trim either sides and all of the text from it. Is that possible?

Re: Trim Field Name

beck_bakytbek — Fri, 19 May 2017 08:55:00 GMT

Hi Kathryn,

could you give more information or screenshot, what do you expect to achieve?

Beck

Re: Trim Field Name

tamilarasu — Fri, 19 May 2017 08:55:39 GMT

Hi Kathryn,

Here is a method.

=TextBetween('SHIP_Night_Week2.xls','Week','.xls')

Re: Trim Field Name

tamilarasu — Fri, 19 May 2017 09:00:04 GMT

=Replace(SubField('SHIP_Night_Week1212.xls','Week',2),'.xls','')

Re: Trim Field Name

Peter_Cammaert — Fri, 19 May 2017 09:00:08 GMT

Or this one:

=KeepChar('SHIP_Night_Week965123.xls','0123456789') & '.xls'

Re: Trim Field Name

khaycock — Fri, 19 May 2017 09:05:18 GMT

How can I use this in the script to load for each file? As currently SHIP_Night_Week is loaded in as a wildcard with many different weeks?

Re: Trim Field Name

Peter_Cammaert — Fri, 19 May 2017 09:11:06 GMT

I'm not sure I understand.

If you mean that you want a link field to some other WeekNr field, just create it during your load. Like in:

LOAD ...

KeepChar(FileName(), '0123456789') AS WeekNumber,

...

FROM [*.xls] (options);

Re: Trim Field Name

khaycock — Fri, 19 May 2017 09:11:41 GMT

How can I use this in the script to load for each file? As currently SHIP_Night_Week is loaded in as a wildcard with many different weeks?

Re: Trim Field Name

tamilarasu — Fri, 19 May 2017 09:15:06 GMT

In that case, you can try something like

Load *,

Textbetween(FileName(),'Week','xls') as Filename

From

....

Load *,

Replace(SubField(FileName(),'Week',2),'.xls','') as Filename

From

....