Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Creator III
Creator III

Remove the SEL0004 field.  That should do it.

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

View solution in original post

7 Replies
jcampbell474
Creator III
Creator III

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
Specialist
Specialist

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;



Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Kushal_Chawda

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;


Anonymous
Not applicable
Author

Thank you! Fast and easy

Anonymous
Not applicable
Author

Thanks. That also worked

Anonymous
Not applicable
Author

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