Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
An option is to create a flag for this in the script.
Can you please 'read' the second option ?
Thanks for your help!
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 -
thanks i think it's better not to do it in the script in my case
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.
also, how would I add restrictions to the expression? (e.g. only sum if CustomerType={'Nice','Justin Bieber Fan'} ) ?
Like this?
=Sum({<[Size from manufacturing]={"=[Size from manufacturing]<>[Size from accounting]"}, CustomerType={'Nice','Justin Bieber Fan'}>} AmountField)
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...??