topic Re: Pick up specific date from a string in QlikView

Pick up specific date from a string

uba_ha — Sat, 16 Nov 2024 20:10:42 GMT

Hello experts, I am new to Qlik and I am trying to pick up some useful information (date) from a string ([Invoice Code]), which looks something like "Connecticut (SAP) - Connecticut Ltd - 4200001444 - WE 06/05/2018 - INV 06/05/2018".

All I want is getting the date of the "WE", i.e. "06/05/2018" from the above (not the date after "INV").

I used PurgeChar(Upper([Invoice Code]),'ABCDEFGHIJKLMNOPQRSTUVXYZ') to get rid of the characters except "W".

I then used

MID(PurgeChar(Upper([Invoice Code]),'ABCDEFGHIJKLMNOPQRSTUVXYZ-'),FindOneOf('W',PurgeChar(Upper([Invoice Code]),'ABCDEFGHIJKLMNOPQRSTUVXYZ-'),10)+3,10)

to find the location number of "W" and from that fetch the next 10 characters but the output is ") 4200".

I have attached the qvw.

Any ideas would be grateful. Many thanks.

Re: Pick up specific date from a string

m_woolf — Thu, 29 Aug 2019 11:58:49 GMT

date(textbetween([Invoice Code],' WE ',' - ') as YourFieldName

Re: Pick up specific date from a string

trdandamudi — Thu, 29 Aug 2019 12:13:32 GMT

One more way to do this is:

= Mid( [Invoice Code],Index ( [Invoice Code], 'WE ' )+3 ,10)

and it will give you the date after 'WE '. Hope this helps.

Re: Pick up specific date from a string

uba_ha — Thu, 29 Aug 2019 14:24:20 GMT

Many thanks for all your help!