Intersection of List box values

[#1]

Hi,

I've a table with the details of Product a company makes..A,B,C,D etc.....

Now in a particular yearmonth the company used to make only Product A & B

& gradually it started making C,D, E etc...& stopped making A etc.

now i want to have two selection boxes for YearMonth- to compare over the two selected periods which are the new products for the latest YearMonth which were not made in the older selected YearMonth.

For this, i've taken a resident load of the YearMonth & Product fields from the main table to create two separate list boxes.

Now i've YearMonth1 & Products 1 from main table; &

             YearMonth 2 & Products 2 from resident table load

if i make selections in the two YearMonth boxes as desired i get the correct Product list in the Products List boxes

Problem: But now i need a list of those products which are present in Product 1 but not in Product 2 & vice-versa also

i.e intersection of the two list boxes.

Plz help

Regards

[swuehl · Accepted solution]

If you want stick to your two tables approach, I think you can do it like this:

For common products, create a straight table chart with dimension Product and expression

=sum({<Product *= p(Product2)>}1)

For removed products, create a straight table chart with dimension Product and expression

=sum({<Product /= p(Product2)>}1)

For new products, create a straight table chart with dimension Product2 and expression

=sum({<Product2 /= p(Product)>}1)

You can hide the expression in all tables in presentation tab, so you get a list of the Products only

See also attached.

Regards,

Stefan

[#3]

Hello Stefan,

i don't have words to say thank you.

Just a bit more explaination needed

i think here /= operator works like minus operator,am i correct?

why do we get a red sign in the expression box whereas the Expression OK is displayed.same for the *= operator

[#4]

Sir,

one more thing: can we show these sum expressions(no. of products) as numbers in some text boxes ?

Regards

[swuehl]

You want something like this?

='Number of common products: '& Count(distinct {<Product  *= p(Product2)>} Product)

The red sign in the expression box is a bug in the syntax checker of the expression box. The expression is ok.

If you want to get rid of the red sign, you can rewrite the expression to e.g.

='Number of common products: '& Count({<Product  =p(Product) * p(Product2)>} Product)

The / or symmetric difference operator works not like the minus or exclusion operator, from the HELP:

/ Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands.

Hope this helps,

Stefan

[#6]

Thank you so much Sir.

Today i've learnt something new in QV...great !!!

Regards

[swuehl]

Your welcome.

If you are using QV11, I think you can also use alternate states instead of the data island table Product2 / Month2.

[#8]

Hello Sir,

Thanks again.

Right now i'm using QV 10 only, will upgrade soon to QV 11 then check this out.

I've one more question:

As i said that the Product is stopped from production, actually once it is produced it can be moved from one category to the other.

Let me explain:

Right now my selection filters are the Category & Type of the Product where each Category is divided into different Types.

Category X,Y,Z

for Category X we have Type 1,Type 2

for Category Y we have Type 3,Type 4

for Category Z we have Type 5,Type 6

so when i took the resident load i also took these fields without changing the names so as to create a synthetic key between the two tables & make the result possible.

1) Now for the New Products(Product1)  for YearMonth1 & YearMonth2 selections:

i would like to see in the sheet the Category and Type columns of the New Product in Year Month2. The problem is if i add the Category/Type column directly to the sheet it would obviously show the selections made in the Category/Type list boxes

2) the same needs to be done for the Removed Products (rather Moved Products), i.e. The products which no longer exist in the YearMonth1 for the selctions Category & Type, i need to show the Category & Type for YearMonth1

3) In ur 1st answer u mentioned that: If you want to stick with two table approach, does the single table approach is the QV 11 (AS) approach or it can be done in QV 10 also in a better way than i'm doing.

Plz help again.

Best Regards

[swuehl]

Could you maybe modify one of my samples to your setting incl. Category and Type and repost it here?

If your tables are linked by Category/Type and you select on these fields, this will also filter the second table.

If you want to clear the selection in your chart objects, you can use something like

='Number of common products: '& Count(distinct {<Product  *= p(Product2), Category=, Type=>} Product)

Not sure if this is what you want.  I haven't fully understood what you want to achieve with linked tables, so a small sample incl your expected outcome might help.

Regards,

Stefan

[#10]

Hello Sir,

I'm posting the application after modifying it with Category/Type.

Please have a look.

Regards