6 Replies Latest reply: Jul 5, 2012 9:41 AM by calangokid RSS

    Fill dates with value or find last value...

      Hi ALL,

      Sorry, but my english is so poor!!!  heheh

      I have a problem with my inventory control... My table loaded from ERP came with balance information in specific dates... I need fill this table, completing dates without information with last information balance at today date... My user select a month like march in 2012 and the balance of product 19 will came NULL, but the real balance is R$ 138,51...

      Anybody help me?? 

      Tanks!!

       

        • Re: Fill dates with value or find last value...

          Sorry!!! I´m so sorry!!!

          I dont post the solution...  I´m so bad!!!    ehhehe

           

          This is my script of the final solution!!!   And it´s finally is ok!!  I´m so happy!!!

           

          PRODUTO:

          LOAD ID_PRODUTO,

               CODIGO_PRODUTO,

               NOME_PRODUTO,

               KM_PREVISTA,

               ID_SUBGRUPO

          FROM

          [C:\Users\Renato\Documents\Open Service\Projetos\Transnet\SIB\QVD\PRODUTO.QVD]

          (qvd);

           

           

           

           

          SALDO_TEMP:

          LOAD ID_EMPRESA,

               ID_ALMOXARIFADO,

               ID_PRODUTO,

               DT_HISTORICO_SALDO AS DT_INICIAL,

               QT_HISTORICO_SALDO,

               VL_MEDIO

          FROM [C:\Users\Renato\Documents\Open Service\Projetos\Transnet\SIB\QVD\HIST_SALDO.QVD] (qvd);

          //WHERE DT_HISTORICO_SALDO >= date('01/01/2011');

           

          //FAÇO ISSO PARA IDENTIFICAR ATÉ QUANDO O ESTOQUE SE MANTEVE INALTERADO

          SALDO:

          LOAD *, IF( ID_EMPRESA = Previous(ID_EMPRESA) AND ID_ALMOXARIFADO = Previous(ID_ALMOXARIFADO) and ID_PRODUTO = Previous(ID_PRODUTO), date(Previous(DT_INICIAL)-1), Today() ) AS DT_FINAL

          RESIDENT SALDO_TEMP

          ORDER BY

               ID_EMPRESA DESC,

               ID_ALMOXARIFADO DESC,

               ID_PRODUTO DESC,

               DT_INICIAL DESC;

          DROP TABLE SALDO_TEMP;

           

          //MONTO UM CALENDÁRIO COM TODAS AS DATAS ATÉ A DATA ATUAL

          MinMaxTab:

          LOAD Min([DT_INICIAL]) as MinTaskStart

          RESIDENT SALDO GROUP BY 1;

          Let varMinDate = peek('MinTaskStart');

          Let varMaxDate = TODAY();

          drop table MinMaxTab;

           

          LET VMinOrderDate = date($(varMinDate));

           

          Calendario:

          Load  date('$(VMinOrderDate)' + Recno() - 1) as Data,

          MonthName(date('$(VMinOrderDate)' + Recno() - 1)) as Mês,

          Year(date('$(VMinOrderDate)' + Recno() - 1)) as Ano

          AutoGenerate (varMaxDate - varMinDate + 1);

           

           

          //AQUI ENTRA O INTERVALMATCH PARA PREENCHER TODAS AS DATA DO INTERVALO

          //exit script;

          SALDO:

          LEFT JOIN (SALDO)

          IntervalMatch (Data)

          LOAD DISTINCT DT_INICIAL, DT_FINAL

          RESIDENT SALDO;

           

          This line below was causing the trouble in my load script... 

          LOAD DT_INICIAL, DT_FINAL

          With a litle correction "all my problemas go to space" !!!   hehehe

          I only put a DISTINCT and a magic so!!!

          LOAD DISTINCT DT_INICIAL, DT_FINAL

          Sorry my poor english em my hummor sense...

          By

          Renato Barbosa