Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
bmchale1983
Contributor III
Contributor III

Join for Max Date with Time

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;

4 Replies
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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
bmchale1983
Contributor III
Contributor III
Author

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
Specialist III
Specialist III

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)