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

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

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!

12 Replies
tresesco
MVP
MVP

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)

srchilukoori
Specialist
Specialist

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

Not applicable
Author

Can you please 'read' the second option ?

Thanks for your help!

peschu123
Partner - Creator III
Partner - Creator III

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 -

Not applicable
Author

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

tresesco
MVP
MVP

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.

Not applicable
Author

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

tresesco
MVP
MVP

Like this?

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

peschu123
Partner - Creator III
Partner - Creator III

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...??