Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
Remove the SEL0004 field. That should do it.
Edit: Do not remove it from the load script. Only remove it from the table object.
Remove the SEL0004 field. That should do it.
Edit: Do not remove it from the load script. Only remove it from the table object.
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;
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.
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;
Thank you! Fast and easy
Thanks. That also worked
Thanks for your answer Yes it worked, but the number 42 under "Total orders" is not nice to have. Can you delete it somehow?