topic Multiple table sort in QlikView

Multiple table sort

agsearle — Tue, 24 May 2011 14:12:53 GMT

I have a report with many tables, but am having problems with one column of data in a chart straight table.

For this column I need to refer to data from a couple of tables like (the Meter Reading Reason is in a separate table to the other fields):

Meter Reading Date Reading Meter Reading Reason

31/10/08 789 -

15/12/08 808 -

16/12/08 808 06

27/04/09 829 -

15/04/10 873 -

16/04/10 873 06

13/11/10 994 -

This is a set of meter readings, and Meter Reading Reason code of '06' indicates a new customer.

I need to have a column that will show the bolded reading of 873. This being the most recent meter reading where the meter reading reason is '06'.

I've tried FIRSTSORTEDVALUE and a number of IF expressions all to no avail, any ideas?

Tue, 24 May 2011 14:17:55 GMT

Why don't you sort the data in the chart by the Meter Reading Reason in a first place, and after that by the date descending?

agsearle — Tue, 24 May 2011 14:23:28 GMT

I only want to display in the straight chart just '873', I don't want to see any of the other data.

There are 4000 accounts in this report, and I only want the relevant reading to show on each one.

Tue, 24 May 2011 14:30:54 GMT

In that case, when sorting, create an expresion by concatenating those two values as I have proposed previously

Tue, 24 May 2011 14:41:24 GMT

Hi,

If you just want to display the '873' you can put the following expression in an text box:

=only({$<[Meter Reading Reason]={06}, [Meter Reading Date]={$(=max([Meter Reading Date]))}>} Reading)

Hope this helps

Regards!

agsearle — Tue, 24 May 2011 14:50:27 GMT

Sorry this didn't work either, even in a text box.

I do need it to be an expression in a chart straight table though.

Tue, 24 May 2011 15:33:04 GMT

Hi,

Attached is an example with a solution, I changed the expression to:

=max({$<[Meter Reading Reason]={'06'}, [Meter Reading Date]={"=max([Meter Reading Date])"}>} Reading)

Maybe you will need to format your date field with the date() function in order for this to work

Regards!

agsearle — Wed, 25 May 2011 09:07:00 GMT

Thanks Gabriela that works!