topic Re: Formulas in QlikView

Formulas

Anonymous — Mon, 04 Jan 2016 09:33:53 GMT

Hia all

I have a pivot based in a spreadsheet...

The spreadsheet has employee number, date and time the employee clocked in or out

The script to load data is:

Load

Funcionario,

Data,

Hora,

rangesum( 1, if(peek('Funcionario') = Funcionario and peek('Data') = Data, peek(Clockout))) as Clockout

FROM

(biff, embedded labels)

The "result" is:

My question is... how can I calculate the time worked in the morning and the afternoon

For instance...employee 20021... I would like to have a column between 2 and 3.... that would calculate 13:10 - 9:10... (column 2 minus column 1)... and get 04:00...etc...

Thanks

Hugo

Re: Formulas

sunny_talwar — Mon, 04 Jan 2016 12:13:16 GMT

Are you wanting to do this in the script?

Re: Formulas

Anonymous — Mon, 04 Jan 2016 12:20:01 GMT

In the script... or in the pivot...

Thanks

Re: Formulas

Kushal_Chawda — Mon, 04 Jan 2016 12:20:39 GMT

may be this link will help

Re: Working hours only

Re: Formulas

Anonymous — Fri, 08 Jan 2016 15:09:33 GMT

In the script... or in the pivot...

Thanks

Re: Formulas

marcus_sommer — Sat, 09 Jan 2016 12:30:49 GMT

Here is one opportunity how it could be solved - with a join approach to bring all to calulate data in one record and which in this case excludes uncompleted records. The rawdata are removed but this could be reversed if some fields would be renamed to avoid synthetic keys.

If you preferred another solution the for the join used counter-fields RecordPair and RecordDelimiter will be useful, too - for examples as flag within a set analysis.

- Marcus