Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted

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

View solution in original post

11 Replies
Highlighted
Creator II
Creator II

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

Highlighted
Creator
Creator

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

Highlighted

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

View solution in original post

Highlighted
Not applicable

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

Highlighted
Specialist
Specialist

Hello!

Maybe this will help:

Convert TimeStamp format

Highlighted
Master
Master

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'))

Highlighted
Specialist
Specialist

Pls try this:

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


EDIT

1.) Corrected time format

DD/MM/YYYY HH:MM:SS - Incorrect

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

Highlighted
Creator
Creator

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.

Highlighted
Specialist
Specialist

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).