Generate Rows Error

Below is my data (all records, limited columns for privacy) from a text input.

I need to create buckets ('t') for the qty in each layer. I want to do these buckets in increments of 500. So layer 10 should only have 1 bucket, with value of 55.49. Layer 9 would have 3 buckets: a 500 bucket, a 1000 bucket, and a 1334.52 bucket.

I can't figure out the generate rows tool to achieve this:

It shows the error message: "The Value did not change after the Loop Expression" and this is my output:

If I filter for a specific layer (1 row flowing into the generate rows tool), it does appear to work - but won't replicate for the entire data set, for example filtering for layer 1:

Just for context, our goal here is to use the generate rows tool to model LIFO inventory accounting.

Preparation

Data Investigation

Accepted answers

FinnCharlton

Here is a slightly hacky way of doing it

Condition Expression = [qty] >= t and [t]>0

Loop Expression = if [qty]=t then t*-1 elseif t+500 > [qty] then (t+([qty]-t)) else [t]+500 endif

All comments

FinnCharlton

Try changing your condition expression to [qty] > [t] ?

davidwwyche

I've tried that as well - it runs without an error, but I don't get the last bucket in each layer I need - the one that maxes out the layer, see below:

FinnCharlton

Here is a slightly hacky way of doing it

Condition Expression = [qty] >= t and [t]>0

Loop Expression = if [qty]=t then t*-1 elseif t+500 > [qty] then (t+([qty]-t)) else [t]+500 endif

davidwwyche

That works - thanks! A few follow-up questions for you:

How/why are you sometimes calling " [t] " vs " t ", I would have thought you always need use " [t] " convention?

And what does " t*-1 " mean? I've never seen that?

Again, really appreciate your help - haven't seen that kind of syntax before and wouldn't have figured that out

FinnCharlton

For some reason the generate rows tool allows you to call fields without the square brackets. t is the same as [t].

t*-1 is multiplying t by -1, i.e. making it negative. This then breaks the [t]>0 requirement of the conditional expression, so the row is not shown and generate rows moves on. Note this will work as long as there are no negative values of [qty] in your data (which I assume there shouldn't be).

If this helped please mark as a solution!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703