topic Comparing new records with old ones in QlikView

Comparing new records with old ones

Wed, 04 Apr 2012 08:26:57 GMT

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"

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

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

Comparing new records with old ones

Thu, 12 Apr 2012 11:57:03 GMT

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

Any help is fantastic!

Thank you

Ramon

Comparing new records with old ones

magavi_framsteg — Thu, 12 Apr 2012 12:34:05 GMT

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

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Comparing new records with old ones

Thu, 12 Apr 2012 12:37:00 GMT

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!

Re: Comparing new records with old ones

swuehl — Thu, 12 Apr 2012 12:37:20 GMT

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.

Comparing new records with old ones

Thu, 12 Apr 2012 13:07:26 GMT

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