Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
Not applicable

Funçao

Ola pessoal, bom dia.

Estou com uma dúvida quanto a modelagem de uma tabela.

*Estrutura Original

ID | Dt_Competencia | Status

1 | 01/01/2010 | A

1 | 03/01/2010 | B

1 | 05/01/2010 | C

3 | xxxxxxxx | Y

*Estrutura Desejada

ID | Dt_Competencia | Status

1 | 01/01/2010 | A

1 | 02/01/2010 | A // Status deve ser igual ao do registro anterior

1 | 03/01/2010 | B

1 | 04/01/2010 | B // Status deve ser igual ao do registro anterior

1 | 05/01/2010 | C

3 | xxxxxxxx | Y

Alguém tem alguma idéia p/ que eu possa chegar a este resultado?

Desde já agradecido.

OBS: Animal essa comunidade do Brasil.

Té...



Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Funçao

Vitor,

Primeiro você precisa criar uma estrutura com todas as datas e depois faz um LEFT Join com as competencias, gerando uma tabela temporária.

O próximo passo é fazer um NOCONCATENATE LOAD (pra não concatenar as tabelas) e usar as funções inter-registro (Previous).

Mais ou menos assim:


LET vDataIni = Date('01/01/2010');
LET vDataFim = Date('06/01/2010');
LET vDif = Interval('$(vDataFim)' - '$(vDataIni)','d');


Tmp_Data:
LOAD DISTINCT
Date('$(vDataIni)' + RecNo() - 1) AS Data
AUTOGENERATE ($(vDif)+1);

LEFT JOIN (Tmp_Data)
LOAD * INLINE [
ID, Data, Status
1,'01/01/2010', A
1,'03/01/2010', B
1,'05/01/2010', C
3,'06/01/2010', Y
];

Datas:
NOCONCATENATE LOAD
if(IsNull(ID),Previous(ID),ID) AS ID,
Data,
if(IsNull(Status),Previous(Status),Status) AS Status
RESIDENT Tmp_Data
WHERE NOT IsNull(Data)
ORDER BY Data ASC;
DROP TABLE Tmp_Data;




2 Replies
Employee
Employee

Funçao

Vitor,

Primeiro você precisa criar uma estrutura com todas as datas e depois faz um LEFT Join com as competencias, gerando uma tabela temporária.

O próximo passo é fazer um NOCONCATENATE LOAD (pra não concatenar as tabelas) e usar as funções inter-registro (Previous).

Mais ou menos assim:


LET vDataIni = Date('01/01/2010');
LET vDataFim = Date('06/01/2010');
LET vDif = Interval('$(vDataFim)' - '$(vDataIni)','d');


Tmp_Data:
LOAD DISTINCT
Date('$(vDataIni)' + RecNo() - 1) AS Data
AUTOGENERATE ($(vDif)+1);

LEFT JOIN (Tmp_Data)
LOAD * INLINE [
ID, Data, Status
1,'01/01/2010', A
1,'03/01/2010', B
1,'05/01/2010', C
3,'06/01/2010', Y
];

Datas:
NOCONCATENATE LOAD
if(IsNull(ID),Previous(ID),ID) AS ID,
Data,
if(IsNull(Status),Previous(Status),Status) AS Status
RESIDENT Tmp_Data
WHERE NOT IsNull(Data)
ORDER BY Data ASC;
DROP TABLE Tmp_Data;




Not applicable

Funçao

<body><p>Caique, obrigado pela for&ccedil;a. </p> <p> </p> <p>Entendi o que vc fez, mas o problema &eacute; que estou trabalhando com Range de Datas. </p> <p>Ex:</p> <p> </p> <p><col width="92"></col><col width="196"></col><col width="121"></col> <tr> <td width="92" class="xl66" height="26"><strong>  Estrutura Original</strong></td> </tr> <tr> <td width="92" class="xl67" height="26"> <table width="409" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="92"> <p align="center"><strong>ID</strong></p> </td> <td valign="bottom" width="196"> <p align="center"><strong>DT_COMPETENCIA</strong></p> </td> <td valign="bottom" width="121"> <p align="center"><strong>STATUS</strong></p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>1</p> </td> <td valign="bottom" width="196"> <p align="center">01/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>1</p> </td> <td valign="bottom" width="196"> <p align="center">03/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>1</p> </td> <td valign="bottom" width="196"> <p align="center">05/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">C</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>2</p> </td> <td valign="bottom" width="196"> <p align="center">01/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>2</p> </td> <td valign="bottom" width="196"> <p align="center">04/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>3</p> </td> <td valign="bottom" width="196"> <p align="center">31/12/2009</p> </td> <td valign="bottom" width="121"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>3</p> </td> <td valign="bottom" width="196"> <p align="center">02/01/2010</p> </td> <td valign="bottom" width="121"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="92"> <p>3</p> </td> <td valign="bottom" width="196"> <p align="center">04/04/2010</p> </td> <td valign="bottom" width="121"> <p align="right">C</p> </td> </tr> </tbody> </table> <br /> <p> </p> <p><strong>  Estrutura Final</strong></p> <table width="429" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" width="72"> <p align="center"><strong>ID</strong></p> </td> <td valign="bottom" width="225"> <p align="center"><strong>DT_COMPETENCIA</strong></p> </td> <td valign="bottom" width="132"> <p align="center"><strong>STATUS</strong></p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>1</strong></p> </td> <td valign="bottom" width="225"> <p align="center">01/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>1</strong></p> </td> <td valign="bottom" width="225"> <p align="center">02/01/2010</p> </td> <td valign="bottom" width="132"> <p>A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>1</strong></p> </td> <td valign="bottom" width="225"> <p align="center">03/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>1</strong></p> </td> <td valign="bottom" width="225"> <p align="center">04/01/2010</p> </td> <td valign="bottom" width="132"> <p>B</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>1</strong></p> </td> <td valign="bottom" width="225"> <p align="center">05/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">C</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>2</strong></p> </td> <td valign="bottom" width="225"> <p align="center">01/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>2</strong></p> </td> <td valign="bottom" width="225"> <p align="center">02/01/2010</p> </td> <td valign="bottom" width="132"> <p>A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>2</strong></p> </td> <td valign="bottom" width="225"> <p align="center">03/01/2010</p> </td> <td valign="bottom" width="132"> <p>A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>2</strong></p> </td> <td valign="bottom" width="225"> <p align="center">04/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>3</strong></p> </td> <td valign="bottom" width="225"> <p align="center">31/12/2009</p> </td> <td valign="bottom" width="132"> <p align="right">A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>3</strong></p> </td> <td valign="bottom" width="225"> <p align="center">01/01/2010</p> </td> <td valign="bottom" width="132"> <p>A</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>3</strong></p> </td> <td valign="bottom" width="225"> <p align="center">02/01/2010</p> </td> <td valign="bottom" width="132"> <p align="right">B</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>3</strong></p> </td> <td valign="bottom" width="225"> <p align="center">03/01/2010</p> </td> <td valign="bottom" width="132"> <p>B</p> </td> </tr> <tr> <td valign="bottom" width="72"> <p align="right"><strong>3</strong></p> </td> <td valign="bottom" width="225"> <p align="center">04/04/2010</p> </td> <td valign="bottom" width="132"> <p align="right">C</p> </td> </tr> </tbody> </table> <p> </p> </td> <td width="196" class="xl68"></td> <td width="121" class="xl69"><br /></td> </tr> </p> <p> </p> <p> </p> <table width="288" cellpadding="0" cellspacing="0" border="0"> <tbody> <tr> <td valign="bottom" colspan="2" width="288"> <p align="center"><strong>Legendas</strong></p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p> </p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p><strong>Novos Registros</strong></p> </td> </tr> <tr> <td valign="bottom" colspan="2" width="288"> <p>Status dos Novos registros =Status Registro Anterior</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p> </p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p><strong>Range Data ID= 1</strong></p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Min</p> </td> <td valign="bottom" width="228"> <p align="center">01/01/2010</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Max</p> </td> <td valign="bottom" width="228"> <p align="center">05/01/2010</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p> </p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p><strong>Range Data ID= 2</strong></p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Min</p> </td> <td valign="bottom" width="228"> <p align="center">01/01/2010</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Max</p> </td> <td valign="bottom" width="228"> <p align="center">04/01/2010</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p> </p> </td> </tr> <tr> <td valign="bottom" width="60"> <p> </p> </td> <td valign="bottom" width="228"> <p><strong>Range Data ID= 3</strong></p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Min</p> </td> <td valign="bottom" width="228"> <p align="center">31/12/2009</p> </td> </tr> <tr> <td valign="bottom" width="60"> <p>Max</p> </td> <td valign="bottom" width="228"> <p align="center">04/04/2010</p> </td> </tr> </tbody> </table> <p> </p> <p> </p> <p>Consigo fazer isso no Oracle, por&eacute;m no Qlik estou tendo dificuldades. </p> <p>Obrigado. </p> <p>Ab&ccedil;o. </p> <p> </p> <p> </p> <td width="196" class="xl67"><br /></td> <td width="121" class="xl68"><br /></td> <p> </p></body>