Problem calculation stocks- null value where no tr... - Qlik Community

Report Inappropriate Content · ‎2011-11-30

A problem calculatin stocks.

We have a stock table, calculaten from stocks form the first date of month and table of all transactions.

The problem is that we don't have transactions- we have null stock on that date.

The question is how to write dates with null stock with the right stock value- shop-date-stock_value-

is we don't have transaction we want to have the same stock value as previous value.

Calculation stocks:

Writing null value with the same.

The problem is that data loads to long.

Stocks:
LOAD * Where date>=MakeDate(2011,09,01);
sql select *
from 1sbkttl;
Stocks :
LOAD
    sc2 as Код_партии,
    if(kind=1,sd) as Sum_Stocks ,
    if(kind=3,sd) as Quantity_Stocks ,
    date as Date_Stocks ,
    sc1 as GOODS,
    sc0 as SHOP
Resident Stocks
Where (trim(accid)='3J' and kind=1 and sd>0) or(trim(accid)='3J' and kind=3 and sd>0) and date>=MakeDate(2011,09,01)
Order by sc0,sc1,date;
DROP Table Stocks;
Stocks:
LOAD
    SHOP,
    GOODS,
    Sum(Quantity_Stocks ) as Quantity,
    SuM(Sum_Stocks ) as Sum,
    Date_Stocks   as Date_stock,
    QuarterName(Date_Stocks ) as квр
Resident Stocks
Group by SHOP,GOODS,Date_Stocks ;
Drop Table Stocks      ;

проводки:
SQL SELECT *
FROM `1sentry`;

ATP:
LOAD
   if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O') ,dtsc0, if((accdtid='8K' and accktid='3J' )or (accdtid='95' andaccktid='3J'), ktsc0)) as SHOP,
    if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O'),dtsc1,if((accdtid='8K' and accktid='3J' ) or (accdtid='95' and accktid='3J'), ktsc1))as GOODS,
    if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O' ),sum) as Sum_прихода,
    if((accdtid='3J' and accktid='57') or(accdtid='3J' and accktid='3Q') or (accdtid='3J' and accktid='3O') ,amount) as Кол_приход,
    if((accdtid='8K' and accktid='3J') or(accdtid='95' and accktid='3J') ,sum) as Sum_расхода,
    if((accdtid='8K' and accktid='3J') or(accdtid='95' and accktid='3J') ,amount) as Кол_расхода,
    date as Date,
    QuarterName(date) as квр
    Resident проводки
where (accktid='57' and accdtid='3J' or (accdtid='8K'and accktid='3J') or (accdtid='95' and accktid='3J') or (accdtid='3J' andaccktid='3Q') or (accdtid='3J' and accktid='3O' )) anddate>=MakeDate(2011,09,01);
drop Table проводки;
b:
LOAD
SHOP,
GOODS,
Sum_прихода,
Кол_приход,
Sum_расхода,
Кол_расхода,
Date
Resident ATP
Order by SHOP,GOODS,Date;
DROP Table ATP;
a:
NoConcatenate
LOAD
    SHOP,
    GOODS,
    Date,
    Sum(Sum_прихода) as Plus,
    sum(Sum_расхода) as Minus,
    Sum(if(IsNull(Кол_приход),'0.000',Кол_приход)) as Plus_quantity,
    Sum(if(IsNull(Кол_расхода),'0.000',Кол_расхода)) as Minus_quantity
Resident b
Group by SHOP,GOODS,Date;
DROP Table b;
Right join (a)
load
Date
Resident Calendar;
left Join(Stocks)
LOAD
    GOODS,
    SHOP,
    QuarterName(Date) as квр,
    Plus,
    Minus,
    Plus_quantity,
    Minus_quantity,
    Date
Resident a
Order by SHOP,GOODS,Date;
DROP Table a;
обороты:
LOAD
Date,
[Date] as DateЗ ,
[SHOP]
,[GOODS]
,[Plus_quantity]
,[Minus_quantity]
,[Quantity],
Plus,
Minus,
квр
, if (   Previous([GOODS])=[GOODS]
    and Previous([SHOP])=[SHOP]
     andPrevious(квр)=квр
   ,
    rangesum(num(Peek(Stock_quantity)),num([Plus_quantity]),-Num([Minus_quantity])),
     rangesum(Num([Quantity]), Num([Plus_quantity]),-Num([Minus_quantity])))as Stock_quantity
   ,   if (   Previous([GOODS])=[GOODS]
    and Previous([SHOP])=[SHOP]
     and Previous(квр)=квр
   ,
     rangesum(num(peek([Stock])), Minus,-Plus),
     rangesum([Sum], Plus,-Minus)) as [Stock],
     RowNo() as н
resident Stocks
order by [SHOP],[GOODS] ,Date;
Drop tables Stocks;

Script adding values instead of nulls

LET n=NoOfRows('обороты');
FOR i = 1 TO $(n)
let date= Peek('ДатаЗ', $(i),'обороты') ;
    let zap=peek('Запас_кол', $(i),'обороты');
    let proj=peek('Код_склада',$(i),'обороты');
    let proj1=peek('Код_склада',$(i)-1,'обороты');
    let t=peek('Код_товара',$(i),'обороты');
    let t1=peek('Код_товара',$(i)-1,'обороты');
    let d=day(peek('ДатаЗ', $(i),'обороты'))-day(peek('ДатаЗ', $(i)-1,'обороты'));
// if peek('Код_склада', $(i),'обороты')='6' and peek('Код_товара', $(i),'обороты')='1BD' then
    if (peek('Код_склада', $(i),'обороты')=peek('Код_склада', $(i)-1,'обороты')) and (Peek('Код_товара', $(i),'обороты')=Peek('Код_товара', $(i)-1,'обороты')) and ((day(Peek('ДатаЗ', $(i),'обороты'))-day(Peek('ДатаЗ', $(i)-1,'обороты'))>1)and (Month(Peek('ДатаЗ', $(i),'обороты'))=Month(Peek('ДатаЗ', $(i)-1,'обороты')))) then
       for j=1 to day(Peek('ДатаЗ', $(i),'обороты'))-day(Peek('ДатаЗ', $(i)-1,'обороты'))-1
           let date=MakeDate(Year(date),num(Month(date)),day(date)+1);
          Concatenate(обороты)
          LOAD * INLINE [Дата,Запас_кол,Приход_кол,Расход_кол,Код_склада,Код_товара
          $(date),$(zap),0,0,$(proj1),$(t1)];
     NEXT
    endif
    endif
    NEXT

Problem calculation stocks- null value where no transaction.