Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone!
I need some help because I've grown tired of trying myself,
and I think you QV professionals can easily help me with this, so please help me!
My problem:
I have a table that look like this(150000rows):Productno,Bill of matrl and some other columns.
productno is the unique ID that consists of x number bill of matrl.
When i select x number of productno in the table, I get x number of rows in the Bill of matrl.
Then i want a new column to show 'yes' or 'no' if any of the selected Bill of matrl rows are
included in the excluded rows Bill of matrl.
Like this:
Table1: selected rows
Productno,Bill of matrl,New column
A A No
A B Yes (BOM 'B' are among the excluded BOM rows)
A C No
B A No
B D Yes (BOM 'D' are among the excluded BOM rows)
B E No
Excluded rows
Productno,Bill of matrl
C B
C D
C F
Best regards/Patric
Patric,
I think this request requires the use of set modifier with implicite field value definitions, i.e. p() and e() functions.
The expression could look like:
=if(count({<[Bill of matrl] = p([Bill of matrl])*p({<Productno= e()>}) >} [Bill of matrl]), 'Yes', 'No')
It might be required to use a second expression
=only({$}1)
to actually show the 'No' lines (due to limiting the Bill of matrl in the first expression).
See also attached.
Stefan
Patric,
I think this request requires the use of set modifier with implicite field value definitions, i.e. p() and e() functions.
The expression could look like:
=if(count({<[Bill of matrl] = p([Bill of matrl])*p({<Productno= e()>}) >} [Bill of matrl]), 'Yes', 'No')
It might be required to use a second expression
=only({$}1)
to actually show the 'No' lines (due to limiting the Bill of matrl in the first expression).
See also attached.
Stefan
Thanks a lot stefan!! it works just fine.
(and you were right, the second expression was needed to show the 'No' lines)
are you also from Sweden?
A follow-up question to the above correct expression:
The table also has a field "level" that is the level of Bill of matrl,
(level 1,2,3,4).
If I want the same expression to work the same, but also compare selected bill
of matrl with excluded bill of matrl at the same level.
How do i write the expression so it works for this?
Patric
It's probably easiest to create a combined field out of Bill of matrl and Level
LOAD
[Bill of matrl] &'-'& level as Combined,
...
then use this Combined field in your set expression instead of [Bill of matrl]:
=if(count({<Combined = p(Combined)*p({<Productno= e()>}) >} Combined), 'Yes', 'No')
Smart!
Thanks alot Stefan.
Patric