topic Re: Read data from right to left in QlikView

Read data from right to left

mwscott1 — Mon, 11 Aug 2014 16:27:20 GMT

I have a column of data "file name" that I am trying to read each item from right to left starting at the end and stopping at the first - that it comes to and load it as "serial number". I have used sub-stings before but only going from left to right with a set amount of characters to read. In this case not all serial numbers are the same length.

data example

I just want to return the part in red.

-685922-685922-1FTSA07019.txt

-699509-699509-1CTSA05422.txt

-704002-704002-1DTSA10880.txt

Re: Read data from right to left

mwscott1 — Mon, 11 Aug 2014 16:29:04 GMT

or really just need what is between the - and the .txt

Re: Read data from right to left

Mon, 11 Aug 2014 16:39:55 GMT

Hi, this can be achieved by using mid function.

= mid('-685922-685922-1FTSA07019.txt', index('-685922-685922-1FTSA07019.txt', '-', -1), (index('-685922-685922-1FTSA07019.txt', '.', -1) - index('-685922-685922-1FTSA07019.txt', '-', -1)) )

Idea is mid (<string>, <index of '-' from right>, <index of '.' from right - index of '-' from right>)

Arun

Re: Read data from right to left

MK_QSL — Mon, 11 Aug 2014 16:45:35 GMT

SubField(FileName,'-',-1) as Result

SubField(SubField(FileName,'-',-1),'.',1) as Result

Re: Read data from right to left

maxgro — Mon, 11 Aug 2014 16:54:59 GMT

'-' & SubField([file name],'-',-1)