Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing new records with old ones

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"

  1. new ProdGroup --> display “1”
  2. new Color --> display “2”
  3. new size --> display “3”

When an expression can be formulated the result will be as follows (ExpressionCheck is the desired column with an expression):

SKUnrProdGroupColorSizeExpressionCheck
X1A1100
X2A2102
X3A1203
X4B1101
X5B1153
X6B2152

I really hope someone could help me, any help will be highly appreciated.

Kind regards

Ramon

5 Replies
Not applicable
Author

Is there anyone who might give me some tips here? I could really use some help.

Any help is fantastic!

Thank you

Ramon

magavi_framsteg
Partner - Creator III
Partner - Creator III

What if a row has both new Color and new Size?

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Not applicable
Author

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!

swuehl
MVP
MVP

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.

Not applicable
Author

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