topic Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match? in QlikView

How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Sun, 20 Oct 2013 09:51:30 GMT

Hi,

I have a model where the same information about the population in the model comes from two fields from different sources (tables). To make it easier, let's say I have a list of SKUs for clothing articles, and each can be a S, M, L in size, and that I get the sizes from manufacturing data and from accounting data.

To validate my data, I want to create a table where only the records where the size values don't match.

So, I thought I'd try to include in the expression something like:

[Size from manufacturing]-=[Size from accounting]

But that didn't do the trick......

what should I do?

Thanks!

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

tresB — Sun, 20 Oct 2013 15:29:59 GMT

May be simply like:

Sum( If([Size from manufacturing] <> [Size from accounting], AmountField))

Or, // using set analysis

Sum({<[Size from manufacturing]={"=[Size from manufacturing]<>[Size from accounting]"}>} AmountField)

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

srchilukoori — Sun, 20 Oct 2013 17:39:30 GMT

An option is to create a flag for this in the script.

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Tue, 22 Oct 2013 07:41:00 GMT

Can you please 'read' the second option ?

Thanks for your help!

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

peschu123 — Tue, 22 Oct 2013 08:17:11 GMT

Hi,

do you want do this in script?

You could do it like:

INPUT:

LOAD * INLINE [

F1, F2

1, 5

2, 4

3, 3

4, 2

5, 1

];

RESULT:

LOAD

F1 AS [Size from manufacturing],

F2 AS [Size from accounting]

RESIDENT INPUT WHERE (F1<>F2);

Regards,

- PS -

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Tue, 22 Oct 2013 08:30:31 GMT

thanks i think it's better not to do it in the script in my case

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

tresB — Tue, 22 Oct 2013 08:30:55 GMT

Seocnd option uses set analysis. The SET part "<[Size from manufacturing]={"=[Size from manufacturing]<>[Size from accounting]"}> is being used like flagging in the script. That is, if the condition (A<>B) satisfies there would be a 'True'(-1) flag for that row, and then those flaggged rows wold be taken into consideration of calculation.

More like the first one, only difference is that, the second one is expected to be a bit faster.

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Tue, 22 Oct 2013 08:33:07 GMT

also, how would I add restrictions to the expression? (e.g. only sum if CustomerType={'Nice','Justin Bieber Fan'} ) ?

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

tresB — Tue, 22 Oct 2013 08:36:16 GMT

Like this?

=Sum({<[Size from manufacturing]={"=[Size from manufacturing]<>[Size from accounting]"}, CustomerType={'Nice','Justin Bieber Fan'}>} AmountField)

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

peschu123 — Tue, 22 Oct 2013 08:41:07 GMT

something like this ins traight table:

IF(match(F1,F2),null(),F1)

PS: Does the set expression work? In my case it didn't work...??

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Tue, 22 Oct 2013 08:44:27 GMT

Pardon me for asking naaive questions, but the part I don't get is the

[Size from manufacturing]=...

I mean if the condition gives (-1), what does it mean to limit the set to X=(-1) ?

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

tresB — Tue, 22 Oct 2013 08:57:47 GMT

In fact it's a very genuine question that might come up in minds of many of us while we see such SET expression. One thing we have to understand that it''s not like searching '-1' value in that field. It's like 'Advanced Search'; like you can do in a list box search. For example:

=sum(Sales)>sum(Budget)

if e.g. invoked from a list box containing the field Salesman, the search would return all salesmen

with an associated sales value larger than their associated Budget.

Hope this clarifies.

Re: How do I add a condition to an expression to only include rows where the values of 2 similar fields don't match?

Tue, 22 Oct 2013 09:01:33 GMT

Huh. Very interesting. Many thanks!