Time Conversation

[bhavvibudagam]

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,

[sunny_talwar]

Try like this:

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

[swuehl]

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

[bhavvibudagam]

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.

[swuehl]

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

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

[sunny_talwar]

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

[swuehl]

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

];

[bhavvibudagam]

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]);

[sunny_talwar]

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

[sunny_talwar]

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]);