Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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...
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;
Screenshot please?