Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Please find the example as attached.
Each SKUnr has a generic number, which means that the SKU creation and its numbers follow a chronological order (X2 is newer than X1).
My goal:
For each SKUnr a check should be done on previous SKUnrs. The first SKUnr is the first reference. There is a logic behind this:
Does SKUnr have: \
0. No reference --> display "0"
When an expression can be formulated the result will be as follows (ExpressionCheck is the desired column with an expression):
SKUnr | ProdGroup | Color | Size | ExpressionCheck |
X1 | A | 1 | 10 | 0 |
X2 | A | 2 | 10 | 2 |
X3 | A | 1 | 20 | 3 |
X4 | B | 1 | 10 | 1 |
X5 | B | 1 | 15 | 3 |
X6 | B | 2 | 15 | 2 |
I really hope someone could help me, any help will be highly appreciated.
Kind regards
Ramon
Is there anyone who might give me some tips here? I could really use some help.
Any help is fantastic!
Thank you
Ramon
What if a row has both new Color and new Size?
Kind regards
BI Consultant
If two or more criteria are met, the expression should display the first encountered difference. This means that the expression should 'show the lowest value'.
Any ideas how to solve this problem?
Thank you!
Ramon,
you could maybe work out a solution by sorting your input table and using peek() function to check for a change in values, like
RESULT:
NoConcatenate
LOAD SKUnr,
ProdGroup,
Size,
Color,
if(isnull(peek(SKUnr)), 0,
if(peek(ProdGroup) <> ProdGroup, 1,
if(peek(Color)<>Color, 2,
if(peek(Size)<>Size, 3)))) as Expressioncheck
resident Sheet1$ order by ProdGroup, Color asc, Size asc;
drop table Sheet1$;
It's kind of unclear to me if a check on change in value is sufficient (Above will e.g. only work if you need to check for a new Size for the same Color, not for a global new Size over all Colors) or if you need to do something more complex.
Above reproduces your expected outcome and may serve you as a starting point.
Hope this helps,
Stefan
edit: If you have loaded the table in above sorted order, you could also use as expression in your table:
aggr(
if(isnull(above(total SKUnr)), 0,
if(above(total ProdGroup) <> ProdGroup, 1,
if(above(total Color)<>Color, 2,
if(above(total Size)<>Size, 3))))
, ProdGroup, Color, Size)
to achieve similar, but regard selections.
Thank you Stefan. If I am correct, the peek function can only be used in the script. Is there a possibility I obtain the same result, but only using an expression in a chart?
Thank you