Set analysis problem!

[Anonymous]

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

[swuehl · Accepted solution]

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

[Anonymous]

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?

[Anonymous]

  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

[swuehl]

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')

[Anonymous]

Smart!

Thanks alot Stefan.

Patric