topic Re: Max field value? in QlikView

Max field value?

Wed, 10 Oct 2012 12:10:46 GMT

Hello,

Please see my attached example.

I would like to show data for all fields but only when the values in the specified field are equal to the Maximum value of that field.

In this case i only want to show rows where journey_length is the largest value specific to the JoinField dimension - i have marked the rows in bold.

I created the expression =aggr(max(journey_length),JoinField) but this does not work as i expected- the MaxLength is not shown against the records i expected.

I had initially planned to modify the Duration expression to sum the Journey_Duration if the MaxLength is equal to avg (journey_length) but as the Max length is not calculated in every row i am stumped.

This has been a difficult question to express in black and white so if there are any questions please get in touch.

Please see the attached spreadsheet for an export of my table along with the expressions.

Regards

Re: Max field value?

Gysbert_Wassenaar — Wed, 10 Oct 2012 12:20:12 GMT

I think you want only Joinfield as dimension and max(journey_length) and avg(journey_length) as expressions.

Re: Max field value?

Wed, 10 Oct 2012 13:06:16 GMT

That doesnt work because it then totalises Duration regardless of whether journey length is equal to the maxlength.

Anyone else have any ideas? can i do it in the load?

Re: Max field value?

Gysbert_Wassenaar — Wed, 10 Oct 2012 14:12:53 GMT

See attached qvw. Is that what you need?

Re: Max field value?

Wed, 10 Oct 2012 15:13:39 GMT

It does seem to work for the extract i posted but i cant make it work for the source data, i'll persevere with it