Coding Experts needed

[Anonymous]

Hi Community,

as I am still new to Qlikview and I have a kind of complex problem, I need your help.

I have a start amount (net inventory) and smaller amounts (production of several weeks) in the same line of my data set.

I need to subtract the first week from the start amount, see if that is bigger than zero, and then proceed, with the next week(s).

As this is kind of hard to understand, I have picture displaying my problem and a corresponding sample data file.

I hope someone can help. I know this can only be solved with variables and loops.

[Anonymous]

Nobody can help? 😞

I know I need some kind of an algorithm, I can write in a pseudo code, like:

Take [FieldStart] and subtract [Field0]

     1) Check if [FieldStart] - [Field0>] > 0   If yes,   Let vNewAmount = [FieldA] - [Field0>] and Let vCounter = 1

     2) Check if $(vNewAmount) - [Field-1] > 0, If yes,   Let vNewAmount  = $(vNewAmount) - [Field-1] and Let vCounter = 2

     3) continue until

     4) Stop if $(vNewAmount) - [Field-X] <= 0

     5) Final result: $(vCounter) * 7 +  (($(vNewAmount) - 0 ) / [Field-X] ) * 7  as [Revenue Days]

But I really don't know how to write that down in a code in Qlik script. I especially have no idea how I get my algorithm to pick different data fields from a data row during the iteration to subtract the value.

I would really appreciate if someone can help me and makes me more knowledgeable for the future because I already found out I improve by every problem a lot.

Thanks a lot for anyone who has a guess. In the post before I have QVW with a simplified problem structure, please note that in my real world problem the data periods are about 52.

[marcus_sommer · Accepted solution]

I'm not sure that I have understood your task completely and your data-example isn't really helpful but I think I would use interrecord-functions like Peek() or Previous() ? within a normal table-structure by transforming the data per The Crosstable Load.

With these functions you could check the records against eachother and calculate your needed values and/or creating some kind of flags. The important point is just to sort your resident-table properly.

- Marcus

[Anonymous]

Thank you Marcus.

Your tip was very valuable to me because it made me think a complete different approach and finally I solved it.

I created a cross table and then some accumulation and ordering and it works very fine.

PRE_WIP:
LOAD * INLINE [
KeyNr,LastName,NetInventory, Period0, Period1, Period2 , Period3, Period4, Period5
1,Braun,95, 20, 45, 25, 30, 5, 20
2,Schulz,200, 34,120,3, 30, 100, 16
3,Meyer,100,25,2,10,20, 5, 10
];

Temp:
CrossTable(Period, Amount,2)
Load KeyNr,LastName,(NetInventory)*-1, Period1, Period2 , Period3, Period4, Period5
Resident PRE_WIP;
Drop Table PRE_WIP;

Temp2:
Load
*,
If(RowNo()=1,Amount,
If(KeyNr = peek(KeyNr),Amount + peek(AmountCumulated),Amount))
as AmountCumulated,
If(RowNo()=1,0,
If(Amount + peek(AmountCumulated) < 0 and KeyNr = peek(KeyNr), 7,
If( Amount + peek(AmountCumulated) >0 and peek(Days) = 7, 0 - (((0 - peek(AmountCumulated))/Amount)*-7),0)))
as Days
Resident Temp;
Drop Table Temp