Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
imrencimen
Contributor III
Contributor III

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

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

View solution in original post

5 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

thank you sunindia‌.

sunny_talwar

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
Contributor III
Contributor III
Author

by mistake

sunny_talwar

Hahahaha okay

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