topic Re: Time Conversation in QlikView

Time Conversation

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

Hi,

Can any one help me to do.

In An 'AHT' fleld i have multiple records like 0:05:03 , 0:04:59 so on.I have to convert this hh:mm:ss format to number.My required format is in AHT fIeld the records are like 303, 299.

Please answer this question in scripting with dynamic expression.

Thanks,

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 08:51:29 GMT

Try like this:

Interval(Time#(AHT, 'h:mm:ss'), 'ss') as AHT

Re: Time Conversation

swuehl — Mon, 02 May 2016 08:51:51 GMT

Try like

LOAD

Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')) as AHTConverted,

...

edit: Note that without using Num#(), you only change the display string of the time interval, not the underlying value.

So if you want to use the field for further calculations, like summing up the values or average them, you need to convert the underlying numeric values.

Data Types in QlikView‌

Get the Dates Right

On Format Codes for Numbers and Dates

Re: Time Conversation

bhavvibudagam — Mon, 02 May 2016 09:19:36 GMT

Hi swuehi,

Thanks for answering.Actually in AHT Field some records are in Time format and some records are in Number format.while using above formula only time is converted to number but remaining number records are not visible.

Re: Time Conversation

swuehl — Mon, 02 May 2016 09:21:34 GMT

Try with the ALT function (assuming number format means default number format).

Alt( Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')), AHT) as AHTConverted,

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 09:22:10 GMT

How does the number formatted AHT look like? Can you share few observation points?

Re: Time Conversation

swuehl — Mon, 02 May 2016 09:48:45 GMT

Can you post some sample lines, e.g. in an INLINE table?

It seems to work for me like

LOAD *,

Alt( Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')), AHT) as AHTConverted

INLINE [

AHT

0:05:03

0:04:59

221

];

Re: Time Conversation

bhavvibudagam — Mon, 02 May 2016 10:06:43 GMT

LOAD

QUEUE,

//"DATE",

Trim(Date(Num(DATE),'MM/DD/YYYY')) as "DATE",

"DAY",

// "MONTH",

//AHT,

Alt(Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')),AHT) as AHTConverted,

W_AHT

FROM [lib://Customer/sheet2.xlsx]

(ooxml, embedded labels, table is [raw data]);

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 11:26:47 GMT

You can also try this:

Table:

LOAD *,

Interval(Alt(Time#(AHT, 'h:mm:ss'), AHT/(24*3600)), 'ss') as NEW_AHT;

LOAD * Inline [

AHT

0:05:03

0:04:59

221

207

];

or this for you:

LOAD

QUEUE,

//"DATE",

Trim(Date(Num(DATE),'MM/DD/YYYY')) as "DATE",

"DAY",

// "MONTH",

//AHT,

Alt(Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')),AHT) as AHTConverted,

Interval(Alt(Time#(AHT, 'h:mm:ss'), AHT/(24*3600)), 'ss') as NEW_AHT,

W_AHT

FROM [lib://Customer/sheet2.xlsx]

(ooxml, embedded labels, table is [raw data]);

Re: Time Conversation

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 13:08:53 GMT

How about this?

LOAD

QUEUE,

//"DATE",

Trim(Date(Num(DATE),'MM/DD/YYYY')) as "DATE",

"DAY",

"MONTH" AS MONTH1,

OFFERED,

ANSWERED,

AHT,

Alt(Num#(Interval(Interval#(AHT,'h:mm:ss'),'ss')),AHT) as AHTConverted,

Num#(Interval(Alt(Time#(AHT, 'h:mm:ss'), AHT/(24*3600)), 'ss')) as NEW_AHT,

W_AHT

FROM [lib://Customer/sheet2.xlsx]

(ooxml, embedded labels, table is [raw data]);

Re: Time Conversation

bhavvibudagam — Mon, 02 May 2016 13:16:33 GMT

Not working still showing 00 for 0:05:03

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 13:19:19 GMT

I see that you have 00 for hh, can you try this:

Interval(Alt(Time#(AHT, 'hh:mm:ss'), AHT/(24*3600)), 'ss')

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 13:30:16 GMT

Can you check if AHT is just a timestamp and doesn't include date component? Can you create a list box with this expression Num(AHT)

Re: Time Conversation

bhavvibudagam — Mon, 02 May 2016 13:45:48 GMT

Hi sunny i have attached the data file.Please find it.Please upload the qvw file if it works fine.

Thanks in advance.

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 14:04:39 GMT

Try this:

Table:

LOAD QUEUE,

DATE,

DAY,

AHT,

Interval(Alt(Time#(NEW_AHT, 'h:mm:ss'), If(Num#(NEW_AHT) < 1, NEW_AHT, NEW_AHT/(24*3600))), 'ss') as NEW_AHT;

LOAD QUEUE,

DATE,

DAY,

Text(AHT) as NEW_AHT,

AHT

FROM

[DATA (7).xlsx]

(ooxml, embedded labels, table is [raw data]);

Table:

LOAD QUEUE,

DATE,

DAY,

AHT,

Interval(Alt(Time#(NEW_AHT, 'h:mm:ss'), If(Num#(NEW_AHT) < 1, Num#(NEW_AHT), Num#(NEW_AHT)/(24*3600))), 'ss') as NEW_AHT;

LOAD QUEUE,

DATE,

DAY,

Text(AHT) as NEW_AHT,

AHT

FROM

[DATA (7).xlsx]

(ooxml, embedded labels, table is [raw data]);

Re: Time Conversation

bhavvibudagam — Mon, 02 May 2016 14:33:05 GMT

Thank you so much sunny its working

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 14:35:01 GMT

Awesome

If you got what you were looking for, I would request you to close this thread by marking correct and helpful answers.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Thanks and regards,

Sunny

Re: Time Conversation

sunny_talwar — Mon, 02 May 2016 14:43:41 GMT

Bhavvi‌ - Please take some time to review the link I have pasted for you. When we ask to mark the correct answer and helpful answer, we are looking for you to mark a answer which actually helped you and not marking any answer as correct.

Why is it important to mark Helpful and correct answers? People like you and me can have similar problems and might be looking on the community for a solution. If they read this discussion, we want to make sure they don't have to spend an hour going through the whole thread to find responses which actually were helpful. So if we leave the current answer as the correct answer, this is what they will see:

How useful do you think that would be for someone looking for a solution to read time correctly?

By marking the truly correct and helpful answer, we both will contribute to making this community a better place. So my friend lets do our share

Thanks and regards,

Sunny