Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

imrencimen
Not applicable

Calculate from Upper Row Value

I have ıtems and demand.

I want to find which demands are provided by me. So that ı find total items stock and distribute that by demans and delivery time. Distributing continues untill stock are run out of ıtems.

I didn't calculate NewStock and So that PreviousNewStock in Qlik. just find red written row.(first rows of Items).

qwd file and excel are attached.

If(Bstock='BB',Previous(NewStock),Bstock) as BStock. but ı cant do it because of duplicate name Bstock.

How can I Calculate "BStock,Previous(NewStock),NewStock" Rows?

    

CustomerItemPrevious ItemDATEWEEKBStockPrevious(NewStock)Demandcan be dispatchNew Stock
BBBzzzzzzzzzzz25.02.20162016-0810228
BBBxxxxxxxxxxzzzzzzzzzzz25.02.20162016-0820222200
BBBxxxxxxxxxxxxxxxxxxxx14.03.20162016-11BBBB=previous(newstock)=20022198
İSTBxxxxxxxxxxxxxxxxxxxx22.03.20162016-12BBBB=previous(newstock)=1985555143
BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=14344139
BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=1391010129
BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=1291616113
BBBxxxxxxxxxxxxxxxxxxxx28.03.20162016-13BBBB=previous(newstock)=113202093
BBBxxxxxxxxxxxxxxxxxxxx04.04.20162016-14BBBB=previous(newstock)=932291
GARxxxxxxxxxxxxxxxxxxxx11.04.20162016-15BBBB=previous(newstock)=911190
BBBxxxxxxxxxxxxxxxxxxxx11.04.20162016-15BBBB=previous(newstock)=90101080
BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=802278
BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=786672
BBBxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=72101062
BOYxxxxxxxxxxxxxxxxxxxx18.04.20162016-16BBBB=previous(newstock)=62303032
BBBxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=322230
BBBxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=308824
GÜRxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=2419195
TELxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=53050
BOYxxxxxxxxxxxxxxxxxxxx25.04.20162016-17BBBB=previous(newstock)=010000
BBByyyyyyyyyyxxxxxxxxxx21.03.20162016-1211411444110
BBByyyyyyyyyyyyyyyyyyyy21.03.20162016-12BB11022108
DOUyyyyyyyyyyyyyyyyyyyy22.03.20162016-12BB108505058
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB582256
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB566650
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB50141436
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB368828
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB2830280
BBByyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB04000
TELyyyyyyyyyyyyyyyyyyyy28.03.20162016-13BB03400
GARyyyyyyyyyyyyyyyyyyyy04.04.20162016-14BB0800
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Calculate from Upper Row Value

May be something along these lines

LOAD *,

  If(If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND'))) < 0, 0,

  If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND')))) as NewStock,

  If(ITEM <> Peek('ITEM'), BStock, Peek('NewStock')) as PreviousNewStock,

  RowNo();

LOAD *,

  If(ITEM <> Peek('ITEM'), ApplyMap('app',ITEM,'Not'),'BB') as BStock;

LOAD * INLINE [

    Customer,ITEM , PreviousItem, DATE, WEEK, DEMAND

    BBB, zzzzzzzzzzz, , 25.02.2016, 2016-08, 2

    BBB, xxxxxxxxxx, zzzzzzzzzzz, 25.02.2016, 2016-08, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 14.03.2016, 2016-11, 2

    İSTB, xxxxxxxxxx, xxxxxxxxxx, 22.03.2016, 2016-12, 55

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 4

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 10

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 16

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 20

    BBB, xxxxxxxxxx, xxxxxxxxxx, 04.04.2016, 2016-14, 2

    GAR, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 1

    BBB, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 10

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 6

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 10

    BOY, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 30

    BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 8

    GÜR, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 19

    TEL, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 30

    BOY, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 100

    BBB, yyyyyyyyyy, xxxxxxxxxx, 21.03.2016, 2016-12, 4

    BBB, yyyyyyyyyy, yyyyyyyyyy, 21.03.2016, 2016-12, 2

    DOU, yyyyyyyyyy, yyyyyyyyyy, 22.03.2016, 2016-12, 50

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 2

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 6

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 14

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 8

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 30

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 40

    TEL, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 34

    GAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 8

    MOB, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 50

    TEL, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 25

    KAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 48

    BOY, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 40

    BBB, yyyyyyyyyy, yyyyyyyyyy, 11.04.2016, 2016-15, 2

];


Capture.PNG

5 Replies
sunny_talwar
Not applicable

Re: Calculate from Upper Row Value

May be something along these lines

LOAD *,

  If(If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND'))) < 0, 0,

  If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND')))) as NewStock,

  If(ITEM <> Peek('ITEM'), BStock, Peek('NewStock')) as PreviousNewStock,

  RowNo();

LOAD *,

  If(ITEM <> Peek('ITEM'), ApplyMap('app',ITEM,'Not'),'BB') as BStock;

LOAD * INLINE [

    Customer,ITEM , PreviousItem, DATE, WEEK, DEMAND

    BBB, zzzzzzzzzzz, , 25.02.2016, 2016-08, 2

    BBB, xxxxxxxxxx, zzzzzzzzzzz, 25.02.2016, 2016-08, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 14.03.2016, 2016-11, 2

    İSTB, xxxxxxxxxx, xxxxxxxxxx, 22.03.2016, 2016-12, 55

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 4

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 10

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 16

    BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 20

    BBB, xxxxxxxxxx, xxxxxxxxxx, 04.04.2016, 2016-14, 2

    GAR, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 1

    BBB, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 10

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 6

    BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 10

    BOY, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 30

    BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 2

    BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 8

    GÜR, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 19

    TEL, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 30

    BOY, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 100

    BBB, yyyyyyyyyy, xxxxxxxxxx, 21.03.2016, 2016-12, 4

    BBB, yyyyyyyyyy, yyyyyyyyyy, 21.03.2016, 2016-12, 2

    DOU, yyyyyyyyyy, yyyyyyyyyy, 22.03.2016, 2016-12, 50

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 2

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 6

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 14

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 8

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 30

    BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 40

    TEL, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 34

    GAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 8

    MOB, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 50

    TEL, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 25

    KAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 48

    BOY, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 40

    BBB, yyyyyyyyyy, yyyyyyyyyy, 11.04.2016, 2016-15, 2

];


Capture.PNG

imrencimen
Not applicable

Re: Calculate from Upper Row Value

thank you sunindia‌.

sunny_talwar
Not applicable

Re: Calculate from Upper Row Value

What is the point of marking your own answer as helpful and that too a thank you response?

I would suggest you to wisely use correct and helpful answers for only those posts that are actually helpful. Read here: Qlik Community Tip: Marking Replies as Correct or Helpful

imrencimen
Not applicable

Re: Calculate from Upper Row Value

by mistake

sunny_talwar
Not applicable

Re: Calculate from Upper Row Value

Hahahaha okay

Made me wonder what was so helpful about your own thank you