Status based on latest date

[gayatri7]

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

[avkeep01]

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;

[gayatri7]

Thanks for your reply!! Let me try  that.