Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table containing a list of partnumbers, supplier of that part number, to what store and the vendor partno which is different from ours.
In this case two suppliers deliver three different partno but we sell it as the same. As you can see in the example below one supplier has two part numbers for our part no and we want to catch those errors.
Partno Store Supplier Sup.Partno
1000 1 A 37
1000 2 A 37
1000 1 A 38
1000 1 B 76
i have a pivot table
with the dimensions
Partno
Supplier
Sup.Partno
and the expression
count(Sup.Partno) because i want to see how many occurences there are (=how many stores have that partno)
In my pivot table i want to show only suppliers that have two partno for one of ours and not show the others.
By checking out the community I thought that my dimension expression would work but it didn't and I would like some help.
Dimension Supplier:
aggr(if(count(distinct Sup.Partno)>1, Supplier, null()), Supplier)
I thought that my dimension expression would work but it didn't
It seems it works.
Just toggle 'Supress when value is null' checkbox.
I must have missed to include another field that messes it up. Because when I do this example myself it works.
I have attached a qvw for you to try. In the right one "A with aggr()" all are visible. But if you click 0101 it shows nothing because the expression doesn't apply after the selection. But according to me it doesn't apply before but it still shows all Partno. I'm missing something. I also included the an extended version of the inline example for you convenience.
Thanks in advance.
As you want select vendors having more than one vendorPartNo in general (not in current selection), correct your set analysis expression:
=aggr(if(count({1<Active={1}>} distinct vendorPartNo)>1, vendor, null()), vendor)
I tried adding the 1 and also tried with $1 but neither of them work properly. All are shown and when clicking either partno the partno is shown even though only 1005 should be shown.
I think I've missed something... why only 1005 ?
Because 1005 has two vendor part numbers for one of ours. And we don't want doubles. It would be problematic to if we bought two different products from a vendor and sold them as the same. Therefore I want to catch the suppliers who have several vendor_part_no for one of our partno.
In this case I guess you use the wrong logic.
As I understand you want to check vendorPartNo count for every pair vendor-partNo
I think this should work:
=aggr(if(Max(aggr(count({1<Active={1}>}distinct vendorPartNo), vendor, partNo))>1, vendor, null()), vendor)
It works for the example qvw but when I apply it to the original data partno that don't apply show up as results. I'll have to dig deeper. Thanks for taking the time helping me though!