Fill dates with value or find last value...

[#1]

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

[#2]

[Jason_Michaelides]

I can't test this at the moment and it may not work in a chart but instead of Sum(Balance) as your expression, try:

If(IsNull(Product),Above(Sum(Balance)),Sum(Balance))

You will probably need to tick the Show All Values checkbox for Date in the dimension tab.

Hope this helps,

Jason

[#4]

Tank you, mery much!!!!

I tested him, but dont solve...

I´m contact with distribute to help me...

Tks

[Jason_Michaelides]

Please post your app so we can help further.

[#6]

Hi,

Search in this forum "FillwithPrevious" or look at Rob Rob Wunderlich "Cookbook kit" fillRowsIntervalMatch.qvw.

In order to find best way to resolve your problems follow the this Rob's posts and others (

Oleg Troyansky

,

John Witherspoon

, ....)

...)

[#7 · Accepted solution]

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

(qvd);

SALDO_TEMP:

LOAD ID_EMPRESA,

     ID_ALMOXARIFADO,

     ID_PRODUTO,

     DT_HISTORICO_SALDO AS DT_INICIAL,

     QT_HISTORICO_SALDO,

     VL_MEDIO

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