2 Replies Latest reply: Oct 14, 2010 5:41 PM by Vitor_Melo RSS

    Funçao

    Vitor_Melo

      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é...



       

        • Funçao
          Caique Zaniolo

          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;




            • Funçao
              Vitor_Melo

              <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>