topic Fill dates with value or find last value... in QlikView

Fill dates with value or find last value...

Wed, 23 May 2012 06:05:37 GMT

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

Fill dates with value or find last value...

Wed, 23 May 2012 06:06:36 GMT

Fill dates with value or find last value...

Jason_Michaelides — Wed, 23 May 2012 06:43:50 GMT

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

Fill dates with value or find last value...

Wed, 23 May 2012 13:18:40 GMT

Tank you, mery much!!!!

I tested him, but dont solve...

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

Tks

Fill dates with value or find last value...

Jason_Michaelides — Wed, 23 May 2012 14:40:51 GMT

Please post your app so we can help further.

Fill dates with value or find last value...

Thu, 07 Jun 2012 11:42:55 GMT

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

, ....)

...)

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

Thu, 05 Jul 2012 13:41:45 GMT

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

Renato Barbosa