topic Re: Text to number Date conversion in QlikView

Text to number Date conversion

Sean_BI — Sat, 16 Nov 2024 19:32:04 GMT

Dear QV community Team -

I've a date field like below which is in text format.

While reading the file in qv script Month should be converted as below

Month

Aug'19

Sep'19

a) Aug

b) I also need month end of each month as 31-Aug-2019, 30-Sep-2019

c) i also need month to be processed as Aug-2019 instead as Aug'19.

I tried to converted the Month like NUM(Date#(Month,'MMM',&'&,'YY')). But it does not work.

Is there any way to convert it.

Rgds,
Sean.

patilamay — Tue, 03 Dec 2019 14:50:05 GMT

Maybe try this

For Aug-2019,

Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY')

For Monthend

MonthEnd( Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY') )

Thanks,

Sean_BI — Tue, 03 Dec 2019 15:23:44 GMT

thanks a lot..

Your text conversion will work, but.

The month value is Aug'19 and not Aug-19. i.e the value has a special character (') highlighted in red.

like i also tried.. Replace(Month, Chr(39),''') as month but this did not work.

For Aug-2019,

Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY')

johnca — Tue, 03 Dec 2019 18:12:29 GMT

Try replacing the apostrophe (character 39 or whatever character is there) with a pipe, or your favorite ascii character) first a la;

Date(Date#(Replace(dates,chr(39),'|'),'MMM|YY'),'MMM-YYYY')

Will transform Aug'19 to Aug-2019, etc...

You should be able to get what you need from that.

HTH,

John

Sean_BI — Wed, 04 Dec 2019 17:00:47 GMT

Thanks Jonh. It worked.