Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
fbsk1907
New Contributor

How to only show max value with two dimensions (date and dealer)

Hi! As you can see in the picture below the maximum value for ex. 2017-06-01 is 42 and it is shown in the column named "Totals orders". However I only want it do be shown once per unique date and the other rows should be deleted. Someone here that knows how to do it? Thanks.

My script:


*Main sheet*

GDS_DATA:
LOAD DATE
[Branch Number],
[SEL0004]

*Main 2 sheet*

LEFT JOIN (GDS_DATA)
PreLoading:
LOAD
       DATE,
       [Branch Number],
MAX (SEL0004) AS [Total Orders]
RESIDENT GDS_DATA
Group by DATE, [Branch Number];

1 Solution

Accepted Solutions
jcampbell474
Contributor III

Re: How to only show max value with two dimensions (date and dealer)

Remove the SEL0004 field.  That should do it.

Edit: Do not remove it from the load script.  Only remove it from the table object.

7 Replies
jcampbell474
Contributor III

Re: How to only show max value with two dimensions (date and dealer)

Remove the SEL0004 field.  That should do it.

Edit: Do not remove it from the load script.  Only remove it from the table object.

kamal_sanguri
Valued Contributor

Re: How to only show max value with two dimensions (date and dealer)

I don't see any reason for having a left join here, Try below script:

********************************************************************


*Main sheet*

GDS_DATA:
LOAD DATE
[Branch Number],
[SEL0004]

*Main 2 sheet*

PreLoading:
LOAD
       DATE,
       [Branch Number],
MAX (SEL0004) AS [Total Orders]
RESIDENT GDS_DATA
Group by DATE, [Branch Number];


Drop Table GDS_DATA;



Re: How to only show max value with two dimensions (date and dealer)

Remove the second LOAD from your script altogether. Your original table GDS_DATA will do.

Then create a simple Straight Table with Dimensions DATE and [Branch Number]. Add a single expression like:

=Max(SEL0004)

and label it as [Total Orders].

The expression will pick the single maximum value for every distinct value combination of fields DATE and [Branch Number]. No additional script code needed.

Re: How to only show max value with two dimensions (date and dealer)

try this

GDS_DATA:
LOAD DATE
[Branch Number],
[SEL0004]

*Main 2 sheet*

inner JOIN (GDS_DATA)

PreLoading:
LOAD
       DATE,
       [Branch Number],
MAX (SEL0004) AS SEL0004
RESIDENT GDS_DATA
Group by DATE, [Branch Number];


Drop Table GDS_DATA;


fbsk1907
New Contributor

Re: How to only show max value with two dimensions (date and dealer)

Thank you! Fast and easy

fbsk1907
New Contributor

Re: How to only show max value with two dimensions (date and dealer)

Thanks. That also worked

fbsk1907
New Contributor

Re: How to only show max value with two dimensions (date and dealer)

Thanks for your answer Yes it worked, but the number 42 under "Total orders" is not nice to have. Can you delete it somehow?

Community Browser