topic Coding Experts needed in QlikView

Coding Experts needed

Anonymous — Tue, 27 Mar 2018 10:58:58 GMT

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.

Re: Coding Experts needed

Anonymous — Wed, 28 Mar 2018 06:17:53 GMT

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.

Re: Coding Experts needed

marcus_sommer — Wed, 28 Mar 2018 09:19:33 GMT

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

Re: Coding Experts needed

Anonymous — Wed, 28 Mar 2018 12:53:52 GMT

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