topic Re: Help me to refine the logic for date field? in QlikView

Help me to refine the logic for date field?

durgabhavani — Tue, 06 Feb 2018 03:25:14 GMT

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

Re: Help me to refine the logic for date field?

durgabhavani — Tue, 06 Feb 2018 04:39:52 GMT

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.

Re: Help me to refine the logic for date field?

jonathandienst — Tue, 06 Feb 2018 04:47:01 GMT

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;

Re: Help me to refine the logic for date field?

jonathandienst — Tue, 06 Feb 2018 04:48:21 GMT

REASON_DATE? Where does that fit in...

Re: Help me to refine the logic for date field?

durgabhavani — Tue, 06 Feb 2018 06:06:31 GMT

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.