Status do último dia do mês anterior na carga (Load Script)

[lucas_fialho]

Boa noite a todos

Preciso buscar qual era o Status de um registro no último dia do mês anterior, mas isso precisa ser feito na carga e não via Set Analysis. 

Tentei usar o Max da data de referência, mas ele trás o max de cada Status e eu só preciso do último. 

Como posso buscar na carga para trazer onde a data de referência seja o último dia do mês anterior. (Tentei com o script que coloquei na imagem mas não funciona). No caso, o resultado esperado é o Status: EM ESPERA do dia 28 de Fevereiro. (Somente essa linha)

[danilostochi]

dados:
LOAD * INLINE [
cod_ref, dt_referencia, ds_status
12345, 27/03/2019, em tratamento
12345, 28/02/2019, em espera
12346, 25/03/2019, em tratamento
12347, 13/02/2019, em espera
];

dados2:

LOAD
cod_ref,
FirstSortedValue(ds_status,dt_referencia) as Status,
FirstSortedValue(dt_referencia,dt_referencia) as PrimeiraData
Resident dados Group by cod_ref;

DROP Table dados;

[lucas_fialho]

Galera,  ainda não consegui resolver o problema acima.
Alguém tem ideia de como posso resolver isso?

É possível colocar um MAX na Cláusula Where?

A Cláusula Where abaixo somente me retorna o último dia de cada mês, porém eu preciso de uma única linha que é a linha do último dia do mês anterior (Exemplo: Hoje é 23/04/19 - portanto preciso que me retorne o status do dia 31/03/2019) - Mas deve retornar SOMENTE essa linha (linha em amarelo)

WHERE DATE(FLOOR(DATA_REFERENCIA),'DD/MM/YYYY') = DATE(FLOOR(MONTHEND(DATA_REFERENCIA)),'DD/MM/YYYY') ;

Coloquei um Max na cláusula Where mas não funciona
DATE(FLOOR(MONTHEND(MAX(INADIM_DT_REFERENCIA),'DD/MM/YYYY')))

Se alguém puder me ajudar, agradeço.
Vlw

[Gabriel_Fernandes]

Boa tarde Lucas,

faça uma resident trazendo a ultima data por CD_REF, depois faz um RIGHT JOIN.

assim você faz um "filtro" só com a ultima data de cada CD_REF

ex:

RIGHT JOIN

LOAD

CD_REF,

MAX(DATA_REFERENCIA) AS DATA_REFERENCIA

RESIDENT STATUS;

[lucas_fialho]

Olá Gabriel

Desculpe, mas não consegui entender esse Resident com Max e depois um right com Max de novo. Mas o fato é que eu alterei meu Script colocando um Max na data, porém retorna o max para cada status. No exemplo que eu dei, como eu tinha 2 status distintos em várias datas, meu max retorna 2 linhas.

[Gabriel_Fernandes]

Então,

você tem 2 ocorrências pro mesmo código (CD_REF), certo?

CD_REF   |    DS_STATUS    | DATA_REFERENCIA

1440463  |    À VENCER        |   31/10/2017

1440463  |      VENCIDO        |    31/03/2019

o exemplo que te passei, vai carregar a tabela STATUS novamente, mas vai trazer só a maior data por código (CD_REF):

RIGHT JOIN

LOAD

CD_REF,

MAX(DATA_REFERENCIA) AS DATA_REFERENCIA

RESIDENT STATUS

GROUP BY CD_REF ;

 CD_REF   |    DATA_REFERENCIA

1440463    |     31/03/2019

fazendo o RIGHT JOIN pela DATA e CD_REF, vai sobrar :

CD_REF  | DS_STATUS   |     DATA_REFERENCIA

1440463 |     VENCIDO     |        31/03/2019

[danilostochi · Accepted solution]

Boa noite,

veja se isso lhe atende...

dados:
LOAD * INLINE [
cod_ref, dt_referencia, ds_status
12345, 25/12/2018, em tratamento
12345, 31/01/2019, em espera
12346, 28/02/2019, em tratamento
12347, 30/03/2019, em espera
12347, 31/03/2019, em espera
];

LOAD date(max(dt_referencia)) as DataMax Resident dados;

//neste ponto você pode filtrar no where , o resultado será a variável vDataMax que pegara sua ultima data pelo max().

vDataMax = Peek('DataMax'); //variável 

NoConcatenate
DadosFim:
LOAD
*
Resident dados where dt_referencia = '$(vDataMax)';

// Fazemos um resident passando no where o valor da variável vDataMax

DROP Table dados;

[lucas_fialho]

Obrigado a ajuda 

Deu certo