topic Re: Join for Max Date with Time in QlikView

Join for Max Date with Time

bmchale1983 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Join for Max Date with Time

Anil_Babu_Samineni — Sat, 23 Dec 2017 02:17:18 GMT

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;

Re: Join for Max Date with Time

bmchale1983 — Sat, 23 Dec 2017 05:04:26 GMT

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...

Re: Join for Max Date with Time

mdmukramali — Sat, 23 Dec 2017 06:08:26 GMT

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;

Re: Join for Max Date with Time

Anil_Babu_Samineni — Sat, 23 Dec 2017 14:18:21 GMT

Screenshot please?