Remove duplicates by only showing max date value

[BOYERWMAP]

Hello,

I created an app that scores leads...  during my testing I noticed one of the fields I'm pulling in is duplicating records by showing all activity dates.  I only want the max date to show.  I've tried using the max function but it comes back as an invalid dimension even though the code looked right.

This is my app data:

And this is the chunk of script that's bringing in the activity dates.  

Any idea how I can get only the max date records coming through either from the script or in the front end?

I've tried:

max([Activity.Planned To Date]) 

max(date([Activity.Planned To Date])

max (date#([Activity.Planned To Date])

It either removes all my dates or comes back as an invalid dimension.  When I try to add a max to the script, it errors out on the load.  

Thanks,

BOYERWMAP

[JuanGerardo]

Hi @BOYERWMAP you should use Max(), but also the corresponding Group By clause:

max("Planned From Date") AS [Activity.Planned From Date],

...

From ...

Where ...

Group By

"Contact Partner", "Calendar day", ..., "Number of Call Attemps" //--> Include all source fields, except "Planned From Date"

JG

[BOYERWMAP]

Thank you so much for your reply!
That is something I haven't tried... I changed my code to this:
LOAD

"Contact Partner" AS [BPARTNER],
Date("Calendar day",'MM/DD/YYYY')AS [Activity.Calendar Day],
"Business Transaction Type Desc",
Date#("Planned From Date",'MM/DD/YYYY') AS [Activity.Planned From Date],
Max(Date#("Planned To Date",'MM/DD/YYYY')) AS [Activity.Planned To Date],
"User Status Desc" AS [Activity.User Status],
"Number of Activities",
"Number of Call Attempts"
FROM [lib://CRM/30_QVD_Extract/SAP_BI_HANA_ZCSAL_C01.QVD]
(qvd)
WHERE [Business Transaction Type Desc]='Lead Specialist' OR 'SFA Task' AND
[User Status Desc]='OPEN'

GROUP BY "Contact Partner","Calendar Day","Business Transaction Type Desc",
"Planned To Date","User Status Desc","Number of Activities","Number of Call Attempts";

However it still errors out... did I put something in wrong?
Thanks,
BOYERWMAP

[JuanGerardo]

Which error? It's difficult to guess at first sight.

JG

[BOYERWMAP]

That is a good question.  I tried to use the debugger and have it give me a breakpoint, but it wouldn't give me any.  Just told me to fix the error on the full load.

I don't think it likes me trying to use a max on the initial data load for that table.  It won't let me do it.  If I use a max on the field in the app, it only works to do it as a measure, but then it blanks out my date.  I need to show the date.

BOYERWMAP