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

[Anonymous]

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];

[jcampbell474 · Accepted solution]

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]

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]

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]

Thank you! Fast and easy

[Anonymous]

Thanks. That also worked

[Anonymous]

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