topic Re: Status based on latest date in QlikView

Status based on latest date

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

Hi All,

I have data which contains the Key_id having the different status. once the keyid moved from "En Process" state to any other state, date is being populated in FECHA column. as shown below

But sometimes the same key_id is still "En Process" State through out the month. (This report contains weekly data)

I have to create a chart which will show the final status of key_id on that particular month. I was able to flag the key_id with all the status based on FECHA column except "En Process" state.

Could any one please help me to get the final status of each key_id.

Attached is the 4 weeks data

Re: Status based on latest date

avkeep01 — Tue, 28 Nov 2017 14:39:25 GMT

Hi Gayatri,

You could try the following script in the load editor;

LEFT JOIN (FechaTable) LOAD

MAX(Fecha Consulta) AS [Fecha Consulta],

1 AS [Last Row]

RESIDENT [FechaTable];

Then you could do a resident load and where the column FECHA is empty and the value of [Last Row] = 1 there you can use the [Fecha Consulta] instead of FECHA.

Example:

Table:

LOAD

IF(FECHA > 0 , FECHA, IF([Last Row] = 1, [Fecha Consulta],NULL())) AS FECHA_new

RESIDENT FechaTable;

DROP FIELD FECHA;

RENAME FIELD FECHA_new TO FECHA;

Re: Status based on latest date

gayatri7 — Tue, 28 Nov 2017 17:51:30 GMT

Thanks for your reply!! Let me try that.