Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please help me to refine the logic for HIRE_DATE field.
Currently HIRE_DATE field using DATE field where REASON='HIRED' from the STATUS table.
Now new logic needs to change like below.
HIRE_DATE field needs to use if there is REASON_DATE where REASON='HIRED' else DATE from the status table.
Please find my below existing code. Also note that no need to change other logic.
STATUS:
LOAD ID,
REASON,
ACTION_REASON,
DATE,
REASON_DATE
FROM
STATUS.QVD(qvd);
OC_Temp:
LOAD ID,
KEY
FROM
EMP.QVD(qvd);
LEFT JOIN(OC_Temp)
LOAD ID,
MIN(IF(UPPER(REASON)='HIRED' OR UPPER(ACTION_REASON)='HIRED',DATE)) AS HIRED_DATE,
MAX(IF(UPPER(REASON)='EXIT',DATE)) AS EXIT_DATE
RESIDENT OC_Temp
WHERE MATCH(UPPER(ACTION),'HIRED','EXIT') OR MATCH(UPPER(ACTION_REASON),'HIRED')
GROUP BY ID;
Thanks in advance.
Durga
small correction in new HIRED_DATE field.
HIRE_DATE field needs to use if there is REASON_DATE else DATE where REASON='HIRED' from the status table.
Possibly like this:
KEY_MAP:
Mapping LOAD ID,
KEY
From EMP.QVD(qvd);
RESULT:
LOAD ID,
ApplyMap('KEY_MAP', ID) as KEY,
REASON,
ACTION_REASON,
DATE,
REASON_DATE
From RESULT.QVD(qvd);
Join(RESULT)
LOAD ID,
Min({<REASON = {'HIRED'}> + <ACTION_REASON = {'HIRED'}>} DATE)) AS HIRED_DATE,
Max({<REASON = {'EXIT'}>} DATE)) AS EXIT_DATE
Resident RESULT
Where Wildmatch(REASON, 'HIRED', 'EXIT') OR WildMatch(ACTION_REASON, 'HIRED')
Group By ID;
REASON_DATE? Where does that fit in...
we need to consider Reason_date like below.
HIRE_DATE field needs to use if there is REASON_DATE else DATE where REASON='HIRED' from the status table.