Set Analysis where Field A = Field B query

[#1]

Hi Guys,

I'm trying to create a straight table using Set analsis where it sums the quantity based on whether the Supplier Code field is equal to the Prime Supplier Field, the Secondary Supplier Field or Tertiary Supplier Field.

To do this i've been using the below Expression;

Expression Title                         Expression

Prime Supplier Qty =                     Sum({$<[Supplier Code]={'=[Supplier Code]=[Prime Supplier]'}>} [Orig. Ordered Qty])

Secondary Supplier Qty =              Sum({$<[Supplier Code]={'=[Supplier Code]=[Secondary Supplier]'}>} [Orig. Ordered Qty])

Tertiary Supplier Qty =                   Sum({$<[Supplier Code]={'=[Supplier Code]=[Tertiary Supplier]'}>} [Orig. Ordered Qty])

However, it doesn't seem to be working correctly;

If also written the expression without set analysis (See right column) and it seems to work so can someone tell me what i'm doing wrong?

Regards,

Nick

[#2]

Thanks Whiteline,

Regarding your previous response above,

sum({$<[Supplier Code]={"=[Supplier Code]=[Prime Supplier]"}>}[Orig. Ordered Qty])

is this the correct expression if i only want to Sum those lines where the Supplier Code IS EQUAL to Prime Supplier? That is what i'm after, SUM Original Ordered Quantity WHERE Supplier Code IS EQUAL TO Prime Supplier.

Please find eattached latest version of file that includes RowNo(). The "Detail Check" table should be showing all the lines from the load in.

[whiteline · Accepted solution]

The expression

sum({$<[Supplier Code]={"=[Supplier Code]=[Prime Supplier]"}>}[Orig. Ordered Qty])

is Incorrect.

Look closely at your data:

[Item Coode], [Supplier Code], [Prime Supplier],

266,               ED0179,            ED0179,

266,               ST0006,            ED0179,

266,               ST0006,            ED0179,

301,               ED0179,           101883

301,               ST0006,           101883

301,               101883,            101883

You just cann't make the selection that you want with [Supplier Code], because there are both situations (bold).

Cosider you are tring to make such selection with a list box [Supplier Code].

So that the only value that works is 101883, because there is no ambiguity.

Use your [RowNo1Check] to make that selection, like this:

sum({$<[RowNo1Check]={"=[Supplier Code]=[Prime Supplier]"}>}[Orig. Ordered Qty])

[#4]

Thanks whiteline, exactly what i'm after.

Regards,

Nick