Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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 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

Tags (2)
Community Browser