topic Re: Select Max Value from a list of values in QlikView

Select Max Value from a list of values

Anonymous — Thu, 26 Mar 2015 20:18:45 GMT

Hi everyone,

I have a table with two dimensions (date and code) and one expression (value).

There are three distinct codes, RFN, NP, and PJ. Each code has a value. The value is a count of the number of times a code occurs on a specific date. I would like to select the max value from each code, and repeat it for the remaining codes on each date.

For example on 04-MAR-15 the max value is 5, so I would repeat 5 for each codes. On 06-MAR-15 the max value is 4 so I would like to repeat 4 for each code.

The table on left is what I currently have. The table on the right is what I'm trying to achieve (these are hard coded currently).

I've tried using the statement:

=if(CODE = 'NP' or CODE = 'PJ', count(distinct {$<prodtime_CODE ={"*"}-{'NP', 'PJ'}>} VALUE), count(distinct {$<CODE ={"*"}-{'NP', 'PJ'}>} VALUE))

but it removes the NP and PJ codes from the table.

Any ideas how I can accomplish this?

Thanks!

Michael

Re: Select Max Value from a list of values

Clever_Anjos — Thu, 26 Mar 2015 20:26:06 GMT

Try

MAX(TOTAL <Date> value)

Re: Select Max Value from a list of values

Anonymous — Thu, 26 Mar 2015 20:30:15 GMT

That removes everything from the table.

Re: Select Max Value from a list of values

Clever_Anjos — Thu, 26 Mar 2015 20:32:46 GMT

Did I typed your field name correctly? value <> Value

Re: Select Max Value from a list of values

Anonymous — Thu, 26 Mar 2015 20:35:06 GMT

Yes, I typed in the field name correctly

Re: Select Max Value from a list of values

JonnyPoole — Thu, 26 Mar 2015 20:39:17 GMT

max( total <Code> aggr( sum(Value),Code,Date) )

Re: Select Max Value from a list of values

Clever_Anjos — Thu, 26 Mar 2015 20:41:28 GMT

Maybe

MAX(TOTAL <Date> AGGR( SUM (Value),Code,Date) )

Re: Select Max Value from a list of values

Anonymous — Thu, 26 Mar 2015 20:45:33 GMT

That is closer, but still not quite right.

It applies the max value between NP and PJ to the NP and PJ codes, but it does not apply the max value of RFN.

Re: Select Max Value from a list of values

JonnyPoole — Thu, 26 Mar 2015 20:47:35 GMT

interesting. i got different results when i typed in your data.

You may have hidden (but important) details in your app that you may need to share...

Re: Select Max Value from a list of values

awhitfield — Fri, 27 Mar 2015 10:39:18 GMT

HI MIchael,

this may seem a daft question, but why do you want to do this, what are you trying to achieve eventually?

Andy

Re: Select Max Value from a list of values

Anonymous — Fri, 27 Mar 2015 20:02:00 GMT

Thanks for the help so far everybody.

Here is a slightly more long-winded version of what I'm trying to accomplish.

I have a multiple employees who can work three types of jobs-- RFN, NP, and PJ. I have a table that includes a date, employee, job type (Code), and hours worked.

What I would like to show is the average amount of hours worked in each code per employee per date.

For example, I had 5 separate employees work on 02-MAR-15. I want to take (sum(RFN hours) / 5), (sum(NP hours) / 5), and (sum(PJ hours) / 5).

Currently, I am only able to divide by the number of employees that have hours worked for the specific job type. So, on 02-MAR-15 for PJ hours I want 1.5 / 5 = 0.3. What I am getting is 1.5 / 1 = 1.5. And for NP hours I want 16.54 / 5 = 3.31, but it returns 16.54 / 4 = 4.135.

Ultimately I want a table with three columns: date, code, and hours. Each date would have a separate row for RFN, PJ, and NP. The hours worked would represent the total amount of time spent working in each code divided by the total number of operators who worked that day.

Thanks for all the help so far. Let me know if there is anything other information that would be pertinent. This in all likelihood much easier than I am trying to make it.

Re: Select Max Value from a list of values

JonnyPoole — Mon, 30 Mar 2015 03:13:35 GMT

My answer is as follows and attached

sum( total <Date,Code> Hours)

count(DISTINCT total <Date> Inits)

Re: Select Max Value from a list of values

Anonymous — Tue, 31 Mar 2015 02:18:44 GMT

Jonathan-- that works perfectly.

Thanks!

Michael