4 Replies Latest reply: Sep 15, 2016 8:10 AM by Clever Anjos RSS

    Quebrar tabela em duas

    Wellington Santos

      Prezados,

       

      Preciso quebrar uma tabela em duas, mas o QV está insistindo em concatená-las. Eu tenho em um mesmo TXT uma tabela pivotada que estou despivotando e a 1ª parte é unidades e a 2ª é reais. Pretendo carregar separadamente para depois fazer um JOIN, para então ter os campos UND e R$ separados. Segue abaixo meu script:


      Parte 1:

      Table:

      CrossTable(MES_ANO, VENDA, 8)

      LOAD

      [@1:8]  AS DATE,

      [@9:10]  AS REGION,

        if(WildMatch([@9:10], '01', '02'), 'SP',

        if(WildMatch([@9:10], '03'), 'RJ+ES',

        if(WildMatch([@9:10], '04'), 'MG',

        if(WildMatch([@9:10], '05'), 'RS',

        if(WildMatch([@9:10], '06'), 'PR+SC',

        if(WildMatch([@9:10], '07'), 'PE+AL+PB+RN',

        if(WildMatch([@9:10], '08'), 'BA+SE',

        if(WildMatch([@9:10], '09'), 'CE+MA+PI',

        if(WildMatch([@9:10], '10'), 'GO+MT+MS+TO+DF',

        if(WildMatch([@9:10], '11'), 'AM+PA+RO+RR+AC+AP', 'VAZIO')))))))))) AS REGION_AGRUP,

      [@11:15] AS PRODUCT_CODE,

      [@16:22] AS PRESENTATION_CODE,

      [@23:32] AS FCC_CODE,

      [@33:37] AS LABORATORY_CODE,

      [@1:8]+[@9:10]+[@11:15]+[@16:22]+[@23:32]+[@33:37] as CHAVE,

      evaluate([@38:52])*1 AS 1,

      evaluate([@53:67])*1 AS 2,

       

      FatoTable:

      NoConcatenate

      LOAD

        DATE,

        REGION,

        REGION_AGRUP,

        PRODUCT_CODE,

        PRESENTATION_CODE,

        FCC_CODE,

        LABORATORY_CODE,

        CHAVE,

          MES_ANO,

          VENDA

      Resident Table;

       

      DROP Table Table;


      Parte 2:

      NoConcatenate

      Table2:

      CrossTable(MES_ANO, VENDA, 8)

      LOAD

      [@1:8]  AS DATE,

      [@9:10]  AS REGION,

        if(WildMatch([@9:10], '01', '02'), 'SP',

        if(WildMatch([@9:10], '03'), 'RJ+ES',

        if(WildMatch([@9:10], '04'), 'MG',

        if(WildMatch([@9:10], '05'), 'RS',

        if(WildMatch([@9:10], '06'), 'PR+SC',

        if(WildMatch([@9:10], '07'), 'PE+AL+PB+RN',

        if(WildMatch([@9:10], '08'), 'BA+SE',

        if(WildMatch([@9:10], '09'), 'CE+MA+PI',

        if(WildMatch([@9:10], '10'), 'GO+MT+MS+TO+DF',

        if(WildMatch([@9:10], '11'), 'AM+PA+RO+RR+AC+AP', 'VAZIO')))))))))) AS REGION_AGRUP,

      [@11:15] AS PRODUCT_CODE,

      [@16:22] AS PRESENTATION_CODE,

      [@23:32] AS FCC_CODE,

      [@33:37] AS LABORATORY_CODE,

      [@1:8]+[@9:10]+[@11:15]+[@16:22]+[@23:32]+[@33:37] as CHAVE,

      evaluate([@938:952])*1 AS 1,

      evaluate([@953:967])*1 AS 2,


      FatoTable2:

      NoConcatenate

      LOAD

        DATE,

        REGION,

        REGION_AGRUP,

        PRODUCT_CODE,

        PRESENTATION_CODE,

        FCC_CODE,

        LABORATORY_CODE,

        CHAVE,

          MES_ANO,

          VENDA

      Resident Table2;

       

      DROP Table Table2;