topic Re: convert to num in QlikView

convert to num

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

Hi all

I have a field like dateJo values like 1-Apr-17 ,1-Jan-17 like so on

when I tried num(dateJo) as NewDate it is giving me null

how to convert this?

Re: convert to num

Anil_Babu_Samineni — Thu, 28 Dec 2017 11:22:30 GMT

May be work like below?

LOAD Num(Month(Date(Date#(dateJo,'DD-MMM-YY')))) as dateJo Inline [

dateJo

1-Apr-17

1-Jan-17

];

Re: convert to num

Anil_Babu_Samineni — Thu, 28 Dec 2017 11:23:28 GMT

Date may not necessary, But some case it will help you. For now, Even this will work

Num(Month(Date#(dateJo,'DD-MMM-YY'))) as dateJo

Re: convert to num

soniasweety — Thu, 28 Dec 2017 11:29:37 GMT

thanks anil its working but

am concatenating two tables with same column but

in one column values showing like 1-Apr-17 and other table column values like below 2017-10-01 00:00:00.000

so how to workout this? to get same result?

Re: convert to num

sasikanth — Thu, 28 Dec 2017 11:31:56 GMT

HI ,

Try this

Num(Date#(dateJo, 'DD-MMM-YY')) as DateNum

Re: convert to num

Anil_Babu_Samineni — Thu, 28 Dec 2017 11:34:12 GMT

I believe this should work

LOAD Num(Month(Date(Date#(dateJo,'DD-MMM-YY')))) as dateJo Inline [

dateJo

1-Apr-17

1-Jan-17

];

Concatenate

LOAD Num(Month(Date(Date#(dateJo,'YYYY-MM-DD hh:mm:ss.fff')))) as dateJo Inline [

dateJo

2017-10-01 00:00:00.000

];

Re: convert to num

PrashantSangle — Thu, 28 Dec 2017 11:34:59 GMT

Use Alt()

See in help menu

Regards,

Prashant Sangel

Re: convert to num

sasikanth — Thu, 28 Dec 2017 11:36:46 GMT

Hi Try this,

LOAD alt(num(Date#(dateJo,'DD-MMM-YY')),num(Date#(dateJo,'YY-MMM-DD'))) as NewDate

Inline [

dateJo

1-Apr-17

17-Jan-01

];

Re: convert to num

Anil_Babu_Samineni — Thu, 28 Dec 2017 11:59:08 GMT

This is not going to work, You need

sasi k wrote:

Hi Try this,

LOAD alt(num(Month(Date#(dateJo,'DD-MMM-YY'))),num(Month(Date#(dateJo,'YY-MMM-DD')))) as NewDate

Inline [

dateJo

1-Apr-17

17-Jan-01

];

Re: convert to num

soniasweety — Thu, 28 Dec 2017 12:14:35 GMT

none of above working its giving null

Re: convert to num

PrashantSangle — Thu, 28 Dec 2017 12:17:13 GMT

can you post your script??

if possible provide sample data.

Regards

Re: convert to num

Anil_Babu_Samineni — Thu, 28 Dec 2017 12:17:14 GMT

Have you tried at least? Copy and paste then reload() and show the image of field to check

Re: convert to num

sasikanth — Thu, 28 Dec 2017 13:40:32 GMT

HI,

i think there might be an issue with date format,

try this

LOAD alt(num(Date#(dateJo,'DD-MMM-YY')),num(Date(dateJo,'DD-MM-YYYY' ))) as NewDate

Inline [

dateJo

1-Apr-17

17-Jan-01

];

LOAD alt(num(Date#(dateJo,'DD-MMM-YY')),num(Date(Date#(dateJo,'DD-MM-YYYY hh:mm:ss' )))) as NewDate

Inline [

dateJo

1-Apr-17

17-Jan-01

];

Re: convert to num

sasikanth — Thu, 28 Dec 2017 13:45:02 GMT

HI,

Code works with out any issues & it will convert a date into number, usage of Month() function will give you month number.

Re: convert to num

shiveshsingh — Fri, 29 Dec 2017 06:50:29 GMT

try this

=num(DATE(Date#(Field,'DD/MM/YYYY'),'mm/dd/yy)')

Re: convert to num

luciano_garcia — Fri, 29 Dec 2017 12:05:29 GMT

Hi soniasweety‌

Check the existence of special chars in your column data (as TAB, or hair spaces, or thin spaces, e.g.)