topic Re: Split en sum duration in QlikView

Split en sum duration

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

Hi all,

I have a text file that as follows: cardnumber, building/floor, time in, time out, duration.

example: c001, Crown/firstfloor, 31/07/18 11:59:58 PM, 01/08/18 12:01:57 AM, 00:01:59

Qlik is new for me so I hope somebody can give me a step in the right direction.

How can I split building/floor and sum duration (duration cardnumber...floor)

Anyone help on this?

Thanks,

Gert

Re: Split en sum duration

Anil_Babu_Samineni — Thu, 18 Oct 2018 03:36:12 GMT

If you can share image of data file and expected result?

Re: Split en sum duration

masterg_nl — Thu, 18 Oct 2018 11:25:17 GMT

Anil,

Some background information, A customer can use a room and have to pay the total time he makes use of the room. The customer makes use of a smartcard to get in and out the room.

I want to know how much time the customer spend in the building and in a room. Occupancy rate per room etc.

I'va added an image of the datafile.

Re: Split en sum duration

jensmunnichs — Thu, 18 Oct 2018 12:36:51 GMT

To split building/floor, you can use the SubField function in script. See the script in the attached file. You cannot do this in the original load, as you cannot perform calculations on fields that haven't been loaded in yet. That's why you have to load in your data first, name that table, then perform a resident load that includes your calculations, and then drop the original table as you no longer need it.

To sum duration and group it by cardnumber/floor/building/anything (I think this is what you mean) you have 2 options. You can either calculate and group the duration in script and create a separate table in the data model (dropping the original ungrouped data afterwards if possible), or you can do it in front end, probably in a straight table.

I only used the second option in the attached file as it's a lot simpler and leaves your data ungrouped for further analysis. Simply add a chart, select Straight table in chart type, add cardnumber/building/floor as dimension (depending on how you want the data to be grouped), then add Sum(duration) as the expression. Keep pressing next until you get to number format, and switch the number format for the expression to Time. Click finish and you should have what you want.

Hope this helps, if not let me know

Re: Split en sum duration

anders_thorngaa — Thu, 18 Oct 2018 14:37:07 GMT

Hi Gert,

Take a look at the attached file. Splitting of Building/floor is done in the code, whereas the calculation of duration is done in the chart.

Output as below.

Re: Split en sum duration

masterg_nl — Thu, 18 Oct 2018 14:54:21 GMT

Hi Anders,

Yes, this is what I try to accomplish. With this is it also possible to filter total duration for example on 'first floor' ?

Re: Split en sum duration

vishsaggi — Thu, 18 Oct 2018 16:30:31 GMT

As Jens mentioned you can do subfield() to split building/floor as

Tab1:

LOAD Keyfield,

Subfield([Building/floor], '/',1) AS Building

Subfield([Building/floor], '/',2) AS Floor

Interval(duration, 'ss') as Seconds

From tablename;

LEFT JOIN(Tab1)

LOAD Keyfield

Sum(Seconds) as Sum_Duration

Resident Tab1

Group By Keyfield;

--> To convert the Sum_Duration to actual time duration you can try like

Interval(Sum_Duration/(24*60*60), 'hh:mm:ss') as TimeDuration

Re: Split en sum duration

jensmunnichs — Fri, 19 Oct 2018 06:15:23 GMT

A pivot table splits the data in order of dimensions. So you'd be able to collapse the floor and building dimensions in this table to show total duration per card number, but not per building or floor. To accomplish that you could either build multiple tables or, probably better yet, use a Cyclic group to be able to swap between table layouts easily without having to rebuild the table.

If you still want to be able to split the data on every level just in a customisable order, but in 1 table, you could do like I did in the attached file and create 3 cyclic groups, and add all 3 dimensions to each (settings > document properties > groups), then use all 3 as dimensions in the pivot table. You can then swap every dimension as you wish using the circular arrows in the dimension columns.

Re: Split en sum duration

anders_thorngaa — Mon, 22 Oct 2018 11:37:33 GMT

Depending on how you want to present the data you could also change the order of dimensions and/or add list boxes as I have done below.