topic Re: How to calculate a Period with 2 Fields ? in QlikView

How to calculate a Period with 2 Fields ?

Tue, 28 Jul 2015 15:49:56 GMT

Hello everyone,

I have a table with 4 Columns :

- Shipment Date

- Shipment Time

- Arrival Date

- Arrival Time

Both of my Date columns contain data in such format : MM/DD/YYYY

As for my time Column, the data is represented as : HH:MM

What I am trying to do is to create a metric that would take into account both time and Date for Arrival and Shipment so that it gives back a Shipping Time Value for each entry of my table. If anyone can help I'd be really graceful.
Thanks a lot
Alexandre

Re: How to calculate a Period with 2 Fields ?

sunny_talwar — Tue, 28 Jul 2015 15:56:53 GMT

So are you trying to find difference between Arrival Date & Time and Shipment Date & Time. If yes, then may be this:

Interval(TimeStamp#([Arrival Date] & ' ' & [Arrival Time], 'MM/DD/YYYY hh:mm') - TimeStamp#([Shipment Date] & ' ' & [Shipment Time], 'MM/DD/YYYY hh:mm'), 'D hh:mm') as Difference

Re: How to calculate a Period with 2 Fields ?

Tue, 28 Jul 2015 16:03:21 GMT

Hi,

I guess you could try to create a Timestamp in the script with date time (MM/DD/YYYY HH:MM) and then calculate the direct diference between the two Timestamps. Timestamp1 - Timestamp2.

Because Timestamps are very memory consuming, I would recommend that you do this in a auxiliary table and then join the results into your original table, and in the end drop the auxiliary table.

Hope It was useful...

Best Regards,

Bruno Silva.

Ps- If you need just to count the working hours, it is a little more tricky than this...

Re: How to calculate a Period with 2 Fields ?

maxgro — Tue, 28 Jul 2015 19:07:14 GMT

Interval(

(Date#( [Arrival Date], 'MM/DD/YYYY') + Time#([Arrival Time], 'hh:mm'))

(Date#( [Shipment Date], 'MM/DD/YYYY') + Time#([Shipment Time], 'hh:mm'))

)

Re: How to calculate a Period with 2 Fields ?

sasiparupudi1 — Tue, 28 Jul 2015 19:15:47 GMT

Please post some sample data

Re: How to calculate a Period with 2 Fields ?

Wed, 29 Jul 2015 08:30:32 GMT

Hello Everyone,

first of all I'd like to thank all the persons that have replied to my question.
I have tried the interval formula that you people provided but the metrics then comes into a weird format (A single number).
What I am looking for is that after measuring the difference between both shipment and arrival, it gives me the result in hours (if it is possible ?).
Again I thank you all for the answers you took the time to write in order to help me. I will recommend this community to anyone

Have a nice day all of you !

Re: How to calculate a Period with 2 Fields ?

Wed, 29 Jul 2015 08:31:28 GMT

I wanted to join my Excel table but I havent managed to do that..
Would you know how to do it ?
Cheers And thank you for answering

Re: How to calculate a Period with 2 Fields ?

jonathandienst — Wed, 29 Jul 2015 08:33:38 GMT

Have a look at this blog posting for ways of handling and analysing multiple date fields:

Canonical Date

Re: How to calculate a Period with 2 Fields ?

sasiparupudi1 — Wed, 29 Jul 2015 08:39:50 GMT

Re: How to calculate a Period with 2 Fields ?

sasiparupudi1 — Wed, 29 Jul 2015 08:45:02 GMT

Please post your excel file

Re: How to calculate a Period with 2 Fields ?

Wed, 29 Jul 2015 08:53:25 GMT

Here is my excel table

Re: How to calculate a Period with 2 Fields ?

qlikmsg4u — Wed, 29 Jul 2015 09:02:56 GMT

Hi Alex,

Try this

Test:

Load *,Round((Timestamp(Date(ArrivalDate)&' '&Time(ArrivalTime),'MM/DD/YYYY hh:mm') - Timestamp(Date(ShippedDate)&' '&Time(ShippedTime),'MM/DD/YYYY hh:mm'))*1440) as Duration;

LOAD [Created Date],

[Created Time],

[Issued Date],

[Issued Time],

[Shipped Date] as ShippedDate,

[Shipped Time] as ShippedTime,

Manifests,

POU,

[Req Qty],

[Issue Qty],

[Req Qty/Vendor],

[Issue Qty/Vendor],

Status,

[Job Refs],

Batches,

[Arrival Date] as ArrivalDate,

[Arrival Time] as ArrivalTime

FROM

Source

Re: How to calculate a Period with 2 Fields ?

sasiparupudi1 — Wed, 29 Jul 2015 09:35:31 GMT

Hi Alexandre,

please try below

Interval(timestamp#(Date([Arrival Date],'D/MM/YYYY')& ' ' & Time([Arrival Time],'H:MM'),'D/MM/YYYY HH:MM')-timestamp#(Date([Shipped Date],'D/MM/YYYY')& ' ' & Time([Shipped Time],'H:MM'),'D/MM/YYYY HH:MM'),'H') as Interval,

Date([Shipped Date],'D/MM/YYYY')& ' ' & Time([Shipped Time],'H:MM') as [Shipped Date Time],

Date([Arrival Date],'D/MM/YYYY')& ' ' & Time([Arrival Time],'H:MM') as [Arrival Date Time];

HTH

Sasi

Re: How to calculate a Period with 2 Fields ?

sasiparupudi1 — Wed, 29 Jul 2015 09:42:03 GMT

Any luck?

Sasi

Re: How to calculate a Period with 2 Fields ?

Wed, 29 Jul 2015 10:11:42 GMT

Thanks a lot it gives me the number of minutes between the 2 dates and so I can convert that into hours

Re: How to calculate a Period with 2 Fields ?

Wed, 29 Jul 2015 10:12:20 GMT

Yes it worked with your solution too ! Thx a lot !

Re: How to calculate a Period with 2 Fields ?

qlikmsg4u — Wed, 29 Jul 2015 10:14:19 GMT

Yes you are right, i calculated duration in minutes..Obviously you can convert these into hours.

If you are satisfied with the answers, please close the discussion so that others will find solutions/answers if they encounter the same situation.