topic Re: Calculate Time format with Number in QlikView

Calculate Time format with Number

Thu, 10 Jul 2014 07:06:05 GMT

Hello everyone,

I got the following question: i need to calculate "Time (in hours)"*"Quantity_per_hour(number)". I am importing data from SQL server, where the field for time is in format "time". Field for quantity is from excel file and its in format number. Is there a way to convert time to number format ? I am doing my calculation as expression.

My idea: for example I got 02:00 hours. I need to convert it to 2.00 decimal so i can multiply it with the quantity.

All ideas will help .

Best regards

Daniel.

Re: Calculate Time format with Number

senpradip007 — Thu, 10 Jul 2014 07:11:47 GMT

Try like

Time([Your Field], 'h')

Re: Calculate Time format with Number

jagan — Thu, 10 Jul 2014 07:13:12 GMT

Hi,

Try like this

=Num(SubField('02:30', ':',1)) + (Num(SubField('02:30', ':',2))/60)

For 2:30 you will get 2.5.

For 2:00 you will get 2.0.

Regards,

Jagan.

Re: Calculate Time format with Number

er_mohit — Thu, 10 Jul 2014 07:14:33 GMT

you might be use replace function

like as above example

num(replace(Timefield,':','.'),'#,#0.00') as Decimal field

here it replace your 02:00 to 2:00

Try this in text object

=num(replace('02:00',':','.'),'#,#0.00')

hope it helps

Re: Calculate Time format with Number

jagan — Thu, 10 Jul 2014 07:16:33 GMT

Hi,

Replace '02:30' with your timefield name.

Regards,

Jagan.

Re: Calculate Time format with Number

Thu, 10 Jul 2014 08:06:38 GMT

Main problem at this point is how to convert TIME format loaded from data base to number:

1. This can happen when data is loading, maybe: num("FIELDNAME") as ........ <- this is not working corrent cause when i give format Number on this data in expression in pivot table it give some different number

2. This can happen in the expression when calculating is done

I got the following code in table field for expression:

=(if([Start time]<=[Break Start Time],if([End Time]>=[Break End Time],[End Time_v2]-[Start time_v2]-([Break End Time_v2]-[Break Start Time_v2]),[End Time_v2]-[Start time_v2]),[End Time_v2]-[Start time_v2])*someNumber (number format or integer)

The output is in format Time at this point and when i convert it to Number from menu it gives wrong data.

Tried to load data like this 2 variants but it didnt help:

Num(time("Starting Time")) as [Start time_v2],

Num(time("Ending Time")) as [End Time_v2],

=====================================

num("Starting Time") as [Start time_v2],

num("Ending Time") as [End Time_v2],

Main goal is to convert that 01:00 to 1.00 number format so i can multiply it with some data in number format and display i correct answer in this field.

Re: Calculate Time format with Number

jagan — Thu, 10 Jul 2014 08:26:48 GMT

Hi,

Try like this in script

LOAD

Num(SubField([Starting Time], ':',1)) + (Num(SubField([Starting Time], ':',2))/60) AS StartTimeNum,

Num(SubField([End Time], ':',1)) + (Num(SubField([End Time], ':',2))/60) AS EndTimeNum;

SELECT

FROM TableName;

Re: Calculate Time format with Number

MarcoWedel — Thu, 10 Jul 2014 08:41:58 GMT

Interval#(Timefield, 'hh:mm')*24

Hope this helps.

Regards

Marco

Re: Calculate Time format with Number

Thu, 10 Jul 2014 08:49:12 GMT

Actualy tried it already:

Num(SubField("Starting Time", ':',1)) + (Num(SubField("Starting Time", ':',2))/60) as [Start time_decimal],

Num(SubField("Starting Time", ':',1)) + (Num(SubField("Starting Time", ':',2))/60) as [End time_decimal],

Num(SubField("Break Start Time", ':',1)) + (Num(SubField("Break Start Time", ':',2))/60) as [Break Start Time_decimal],

Num(SubField("Break End Time", ':',1)) + (Num(SubField("Break End Time", ':',2))/60) as [Break End Time_decimal];

Output in pivot table when i do my calculation as expression:

=(if([Start time]<=[Break Start Time],if([End Time]>=[Break End Time],[End time_decimal]-[Start time_decimal]-([Break End time_decimal]-[Break Start time_decimal]),[End time_decimal]-[Start time_decimal]),[End time_decimal]-[Start time_decimal]))

is "-" for each record.

Re: Calculate Time format with Number

jagan — Thu, 10 Jul 2014 08:57:07 GMT

Hi,

Check the

=(if([Start time]<=[Break Start Time],

if([End Time]>=[Break End Time],

[End time_decimal]-[Start time_decimal]-([Break End time_decimal]-[Break Start time_decimal]),

[End time_decimal]-[Start time_decimal]),

[End time_decimal]-[Start time_decimal]))

You expression seems to be ok, but check the data.

Are you getting the time values in this fields [Start time_decimal], [End time_decimal] ?

Regards,

Jagan.

Re: Calculate Time format with Number

Thu, 10 Jul 2014 09:24:09 GMT

Num(SubField("Starting Time", ':',1)) + (Num(SubField("Starting Time", ':',2))/60) as [Start time_decimal],

Num(SubField("Starting Time", ':',1)) + (Num(SubField("Starting Time", ':',2))/60) as [End time_decimal],

"Starting Time" + "Ending time"+"Break Start Time"+"Break End Time" are time format. What I tried is to show just [Start time_decimal] as expression to see if it works. The output is "-" for all data formats from menu : Number, Interval, Time and so on....

Re: Calculate Time format with Number

MarcoWedel — Sun, 03 Aug 2014 23:03:57 GMT

Hi Daniel,

maybe these examples help:

for genuine time fields:

for text fields containing time strings:

regards

Marco