topic Load TimeWithZone field from postgres in QlikView

Load TimeWithZone field from postgres

Anonymous — Tue, 12 Dec 2017 12:11:39 GMT

Hi,

I have trouble loading data from postgres field of type TimeWithZone.

I can normally load it, and see it in Data model viewer.

The problem is when I try to get min(), max() or amy function. It always return NULL.

So let say that I have "time" field in postgres of TimeWithZone type.

LOAD

min(time) as start,

max(time) as stop

Resident temp_database

group By id;

Return all null rows.

I even try

LOAD

min(Time(time)) as start,

max(Time(time)) as stop

Resident temp_database

group By id;

and

LOAD

min(Time#(time)) as start,

max(Time#(time)) as stop

Resident temp_database

group By id;

I also try

LOAD

Num(time) as nTime,

Chr(time) as cTime

Resident temp_database;

return all null rows.

How to handle this type of fields?

Re: Load TimeWithZone field from postgres

Anil_Babu_Samineni — Tue, 12 Dec 2017 12:16:03 GMT

First you need to play with Time Format from TimeZone?

Re: Load TimeWithZone field from postgres

Anonymous — Tue, 12 Dec 2017 12:21:42 GMT

I found that I was wrongly choose chr() insted of Text()

Now I have : Time#(Text(time)) -> it return values

but

min(Time#(Text(time))) -> return null

Re: Load TimeWithZone field from postgres

Anil_Babu_Samineni — Tue, 12 Dec 2017 12:24:22 GMT

I am not sure what inputs is having from time?

Re: Load TimeWithZone field from postgres

tresB — Tue, 12 Dec 2017 12:24:37 GMT

If the field is being treated as string, you might have to use parsing functions like time#(), date#(), timestamp#() with proper format. Try like:

Timestamp(Max(Timestamp#(time,'format') )) as Stop

'format' - is the one how you see when the field is loaded without max/min.

Re: Load TimeWithZone field from postgres

Anonymous — Tue, 12 Dec 2017 12:26:48 GMT

NUra is Num(time)

WUra is Time#(Text(time))

Those two rows are

min(Time#(Text(time)))

max(Time#(Text(time)))

Re: Load TimeWithZone field from postgres

Anil_Babu_Samineni — Tue, 12 Dec 2017 12:35:20 GMT

Till you are using Text() it will always return text format only.

Re: Load TimeWithZone field from postgres

Anonymous — Tue, 12 Dec 2017 12:37:37 GMT

Sorry but I don't understand you.

I use Time#(Text(time)) so, it should return Time not Text...

Could you explain what do you mean by your comment.

Re: Load TimeWithZone field from postgres

Anonymous — Tue, 12 Dec 2017 12:38:19 GMT

Hi,

Could you look to my reply to other answers.

Re: Load TimeWithZone field from postgres

Anil_Babu_Samineni — Tue, 12 Dec 2017 12:41:35 GMT

Difficult to answer, Without having any inputs. Can you provide inline and result set?May be this

Time(Time#(time), 'hh:mm:ss')

Re: Load TimeWithZone field from postgres

tresB — Tue, 12 Dec 2017 12:44:27 GMT

What if you try like:

Timestamp(Max(Timestamp#(time,'hh:mm:ss+00') )) as Stop