Help me to refine the logic for date field?

[durgabhavani]

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

[durgabhavani]

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.

[jonathandienst]

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;

[jonathandienst]

REASON_DATE? Where does that fit in...

[durgabhavani]

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.