# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

## Problem calculation stocks- null value where no transaction.

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 SHOPResident 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_расхода,Кол_расхода,DateResident ATPOrder by SHOP,GOODS,Date;DROP Table ATP;a:NoConcatenateLOAD    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_quantityResident bGroup by SHOP,GOODS,Date;    DROP Table b;    Right join (a)load   DateResident Calendar; left Join(Stocks)LOAD    GOODS,    SHOP,    QuarterName(Date) as квр,    Plus,    Minus,    Plus_quantity,    Minus_quantity,    DateResident aOrder 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 Stocksorder by [SHOP],[GOODS] ,Date;Drop tables Stocks;