Extract the last 6 characters from a file name? (file will be a table loaded)

[jblomqvist]

Hi all,

How do I get the last 6 characters from a file name?

For example I may have:

C:\Users\Monthly FIle 31012016.xlsx

I would like to get the bold bit from the file path.

Esentially this is a table I am loading but I need the date to record how up to date the data is.

I imagine this can be stored as a variable.

Any idea how I can do this please?

[jblomqvist]

Hi Sunny,

I get this:

I have to make it work in Qlik Sense as well.

The first X does not trace

The second trace works.

[sunny_talwar]

It's strange... can you describe how you plan to use this information?

[jblomqvist]

I want to use it eventually to create buckets. E.g. From 31012017 I want to know how many orders I had in the last 12 months, and how many orders are in the pipeline in the next 12 months.

I tried doing this also but does not work:

Let X = Num#(Subfield(Right('$(vFileLocation)',15),'.',1), '######');

TRACE $(X);

Let Y = Date($(X), 'DD/MM/YYYY');

[sunny_talwar]

I just keeping your variable as a number in that case, because you can always format a number into a date format, but even if we get it to work, the date will saved to the variable as a text instead of a dual date field.

Let X = Num(Date#(Subfield(Right('$(vFileLocation)',15),'.',1), 'DDMMYYYY'));

and when you use this, you can wrap around it with Date

Date($(X))

[jblomqvist]

When I do that the X variable holds nothing

[sunny_talwar · Accepted solution]

Can you try this:

Let X = Num(Date#(Trim(Subfield(Right('$(vFileLocation)',15),'.',1)), 'DDMMYYYY'));

[jblomqvist]

You are a star Sunny! Thank you very much

[sunny_talwar]

Now the date should also work (if you want to use date)

[jblomqvist]

Yes it does