Join for Max Date with Time

[bmchale1983]

I had achieved a successful data model, but I brought time into date and tried to replace with this field but no longer working(did something with join statement, but since I have override save for reload it erased.)  Essentially I want the most recent status date flag, but I keep getting an invalid expression error.  Any help would be greatly appreciated.   It's most likely obvious, just been troubleshooting it to long....

CCMS:
LOAD
CCMS.COMPLAINT_NUMBER&'-'&CCMS.STATUS_CHANGE_DT_TM AS Key,
CCMS.COMPLAINT_NUMBER as COMPLAINT_NUMBER,
CCMS.COMPLAINT_NUMBER,
CCMS.COMPLAINT_LOG_DATE,
CCMS.COMPLAINT_CLOSE_DATE,
CCMS.COMPLAINT_CLOSE_DT_TM,
CCMS.EMPLOYEE_NAME,
CCMS.STATUS,
CCMS.CUSTOMER_TYPE,
CCMS.COMPLAINT_TYPE,
CCMS.COMPLAINT_REASON,
CCMS.COMPLAINT_SUBREASON,
CCMS.ELEVATED_OR_ESCALATED,
CCMS.DUE_DATE,
CCMS.DEPARTMENT_NAME,
CCMS.DEPT_TYPE,
CCMS.COST_CTR_NUM, 
CCMS.STATUS_CHANGE_DT_TM,
if(CCMS.PRODUCT='','Not Disclosed',CCMS.PRODUCT)as CCMS.PRODUCT,
CCMS.STATUS_CHANGE_USER,
num(CCMS.COMPLAINT_CLOSE_DATE-CCMS.STATUS_CHANGE_DT_TM,'#,###') as CCMS.DaysOpen
FROM
[$(SourcePath3)\ComplaintsMaster.qvd]
(qvd);

LEFT JOIN(CCMS)
MAX:
LOAD COMPLAINT_NUMBER&'-'&CCMS.STATUS_CHANGE_DT_TM as Key,
COMPLAINT_NUMBER as COMPLAINT_NUM,
IF(MAX(CCMS.STATUS_CHANGE_DT_TM),1,0) AS MAX_DT_FLAG
RESIDENT CCMS
GROUP BY COMPLAINT_NUMBER;

[Anil_Babu_Samineni]

May be this?

CCMS:
LOAD CCMS.COMPLAINT_NUMBER&'-'&CCMS.STATUS_CHANGE_DT_TM AS Key,
CCMS.COMPLAINT_NUMBER as COMPLAINT_NUMBER,
CCMS.COMPLAINT_NUMBER,
CCMS.COMPLAINT_LOG_DATE,
CCMS.COMPLAINT_CLOSE_DATE,
CCMS.COMPLAINT_CLOSE_DT_TM,
CCMS.EMPLOYEE_NAME,
CCMS.STATUS,
CCMS.CUSTOMER_TYPE,
CCMS.COMPLAINT_TYPE,
CCMS.COMPLAINT_REASON,
CCMS.COMPLAINT_SUBREASON,
CCMS.ELEVATED_OR_ESCALATED,
CCMS.DUE_DATE,
CCMS.DEPARTMENT_NAME,
CCMS.DEPT_TYPE,
CCMS.COST_CTR_NUM, 
CCMS.STATUS_CHANGE_DT_TM,
if(CCMS.PRODUCT='','Not Disclosed',CCMS.PRODUCT)as CCMS.PRODUCT,
CCMS.STATUS_CHANGE_USER,
num(CCMS.COMPLAINT_CLOSE_DATE-CCMS.STATUS_CHANGE_DT_TM,'#,###') as CCMS.DaysOpen
FROM
[$(SourcePath3)\ComplaintsMaster.qvd]
(qvd);

LEFT JOIN(CCMS)
MAX:
LOAD CCMS.COMPLAINT_NUMBER as COMPLAINT_NUM,
MAX(CCMS.STATUS_CHANGE_DT_TM) AS MAX_DT_FLAG
RESIDENT CCMS GROUP BY CCMS.COMPLAINT_NUMBER;

Inner Join(CCMS)

If(CCMS.STATUS_CHANGE_DT_TM = MAX_DT_FLAG, 1,0) as Flag

Resident CCMS;

[bmchale1983]

No this did not work getting red for inner join, plus I had it working without date with one Left Join, but for some reason it does not want to cooperate...

[mdmukramali]

Hi,

Maybe Can you try something like this.

Small adjustment in Anil script.

LEFT JOIN(CCMS)
MAX:
LOAD CCMS.COMPLAINT_NUMBER,

MAX(CCMS.STATUS_CHANGE_DT_TM) AS MAX_DT_FLAG
RESIDENT CCMS GROUP BY CCMS.COMPLAINT_NUMBER;

Instead of inner join.

Noconcatenate

Load *,

If(CCMS.STATUS_CHANGE_DT_TM = MAX_DT_FLAG, 1,0) as Flag

Resident CCMS;

Drop Table CCMS;

[Anil_Babu_Samineni]

Screenshot please?