topic Re: read and transforming in QlikView

read and transforming

userid128223 — Mon, 29 May 2017 19:58:40 GMT

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.

Re: timestamp question

ElizaF — Mon, 29 May 2017 20:24:09 GMT

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

Re: timestamp question

userid128223 — Mon, 29 May 2017 20:38:08 GMT

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

Re: read and transforming

MarcoWedel — Mon, 29 May 2017 22:29:05 GMT

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

Re: read and transforming

Mon, 29 May 2017 22:41:52 GMT

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

Re: read and transforming

Sergey_Shuklin — Tue, 30 May 2017 04:24:20 GMT

Hello!

Maybe this will help:

Convert TimeStamp format

Re: read and transforming

ahaahaaha — Tue, 30 May 2017 07:35:35 GMT

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

Re: read and transforming

kamal_sanguri — Tue, 30 May 2017 07:49:47 GMT

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

Re: read and transforming

userid128223 — Tue, 30 May 2017 08:55:33 GMT

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.

Re: read and transforming

kamal_sanguri — Tue, 30 May 2017 09:07:58 GMT

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

Re: read and transforming

kamal_sanguri — Tue, 30 May 2017 09:21:17 GMT

Coming on to your second question, where you want to subtract 4 hours from your time.

Replace Now() with your expression to calculate date/time

-- Date(Now()-(240/24/60),'DD/MM/YY hh:mm:ss')

**240 represents number of minutes you want to subtract.

EDIT

1.) Please change the Date format 'DD/MM/YY hh:mm:ss' inside the date function as per your need.

2.) corrected date format used upper case characters to show time.

Re: read and transforming

kamal_sanguri — Tue, 30 May 2017 09:30:29 GMT

=date(now()-date(date#('04:00:00', 'hh:mm:ss')),'DD/MM/YY hh:mm:ss')