Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amiumi66
New Contributor III

read and transforming

i have field like below. I need to convert it in promper format so i can do calcualtion on it. like convert from one time zone to another.

Date Time:

02/21/2017 22:02:33          //  I see date & time left alligned.

This is not being read as proper format hence i cannot do any conversion.

1 Solution

Accepted Solutions

Re: read and transforming

Hi,

left aligned Date Time values indicate text values instead of proper dual timestamp values.

You could try to load this field changing your script like

LOAD TimeStamp#([Date Time],'MM/DD/YYYY hh:mm:ss') as [Date Time]

FROM YourSource ...

hope this helps

regards

Marco

11 Replies
elizafilip_2014
Contributor II

Re: timestamp question

Maybe because the field name is [Date Time] and in expression you used dateTime?

amiumi66
New Contributor III

Re: timestamp question

sorry thats just the title on the field. please ignore that its not an issue.

Re: read and transforming

Hi,

left aligned Date Time values indicate text values instead of proper dual timestamp values.

You could try to load this field changing your script like

LOAD TimeStamp#([Date Time],'MM/DD/YYYY hh:mm:ss') as [Date Time]

FROM YourSource ...

hope this helps

regards

Marco

Not applicable

Re: read and transforming

Hello,

Check the examples of the Qlik help for date and time functions and do some tests.

You'll be using those functions often in the future.

,KR Koen

serj_shu
Valued Contributor

Re: read and transforming

Hello!

Maybe this will help:

Convert TimeStamp format

ahaahaaha
Honored Contributor

Re: read and transforming

Hi

You may use for convert in your date format

TimeStamp(TimeStamp#([Date Time], 'MM/DD/YYYY hh:mm:ss'))  or

Date(TimeStamp#([Date Time], 'MM/DD/YYYY hh:mm:ss'))

kamal_sanguri
Valued Contributor

Re: read and transforming

Pls try this:

=Date(Date#('02/21/2017 22:02:33 ','DD/MM/YYYY HH:MMSmiley FrustratedS'),'DD/MM/YYYY HH:MMSmiley FrustratedS')


EDIT

1.) Corrected time format

DD/MM/YYYY HH:MMSmiley FrustratedS - Incorrect

DD/MM/YYYY hh:mm:ss - Correct

amiumi66
New Contributor III

Re: read and transforming

Your suggestion seems to work in script but when i try it in text box it will not work. Any clue why.

Second question is how can i do a GMT to EST converstion from the date, which is -4 hours.

kamal_sanguri
Valued Contributor

Re: read and transforming

Select only one date after you put this expression in text box. The reason behind this is, if you use a field name it will have multiple values and in normal condition text box can't handle this.

If you still want to use field name without selecting any value , try this way-

=concat([your expression],' ,')

This will concatenate all the dates separated by "," (comma).