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]

You'll need to define your expected outcome, for example if you select Month = 1, Month2 = 4, Category = X.

What do you want to see in the table?

[#3]

Dear Sir,

Please find the file attached.

Regards

[swuehl]

This is a little more tricky, please check attached.

[#5]

Dear Sir,

Thanks for you efforts for me.

Please see the file attached & the remarks over there.

Regards

[swuehl]

Sorry, I don't get your point in this sample.

a)

Common products <-> "from the load script these values can be verified"

Is there a problem here?

b) New products (in month) <-> "

when 2nd & 3rd expressions are disabled then the Products are correct & as soon as they are enabled it

shows all the products having that category & type. (Here) when the expressions are enabled

then both B & C are dispalyed (since B also has the same Category & Type as C in Month2)

while D is not displayed at all."

I believe that's not just a cloned object you show here. The 2nd and 3rd expression of your table are missing the if-statement of my original expression, which I introduced to limit the lines shown to the relevant products.

c) Removed Products <-> "

when 2nd & 3rd expressions are disabled then the Products are correct.

For this case, A & E are removed from Month 2(sorry i wrote it Month on the sheet title....it is very confusing for me also)

and from the load script it is evident that A doesn't appear in Month4 which is the maximum Month which means that it is SOLD.

E's status on Removed Products sheet shows correct Category & Type which is similar to the case in New Products Sheet(IN Month)

for C's status"

Again, I don't see an issue here...

[#7]

Dear Sir,

a)

Common products <-> "from the load script these values can be verified"

Is there a problem here?  No problem,thats OK

b) New products (in month) <-> "

when 2nd & 3rd expressions are disabled then the Products are correct & as soon as they are enabled it

shows all the products having that category & type. (Here) when the expressions are enabled

then both B & C are dispalyed (since B also has the same Category & Type as C in Month2)

while D is not displayed at all."

I believe that's not just a cloned object you show here. The 2nd and 3rd expression of your table are missing the if-statement of my original expression, which I introduced to limit the lines shown to the relevant products.

When i saw that the result is not correct, i tried removing the if from expr 2 & expr 3 & also disabled expression 1 but the products were still incorrect. After that i just disabled expr 2 & expr 3 completely (red cross sign) & enabled expr1 Since expr 2 & expr 3 are disabled so it hardly matters that i put an if there again.

the main thing here is that : The correct New Products are products C & D (shown in the sheet with disabled expr 2 & expr3) while the left most sheet with expr 2 & expr 3 show B & C,of which C is correct but B is wrong & D is not at all shown. It is clearly evident from the inline data that B was not a new product for the current selections(Month=4, Category=Y,Type=Type3) as compared to Month=4,Category=Y,Type=Type3)

The primary requirement of the New Products sheet is that the Product column should show the Products which were not there in Month2 but appear in Month1 (Month List box) for the selected Category & Type. (Products C and D here,not B)

c) Removed Products <-> "

when 2nd & 3rd expressions are disabled then the Products are correct.

For this case, A & E are removed from Month 2(sorry i wrote it Month on the sheet title....it is very confusing for me also)

and from the load script it is evident that A doesn't appear in Month4 which is the maximum Month which means that it is SOLD.

E's status on Removed Products sheet shows correct Category & Type which is similar to the case in New Products Sheet(IN Month)

for C's status"

Again, I don't see an issue here...

Same thing here: The primary requirement of the Removed Products sheet is that the Product column should show the Products which were there in Month2 but not in Month1 (Month List box) for the selected Category & Type. (Products A and E here)

Also, B is shown in both the sheets: New Products and Removed Products. This is not possible as a product can either be New or Removed for a particular selection combination

Removed  means a product which was there in Month2 but not anymore in Month1(Month here) for the selected Category & Type.Such products are A and E but the left sheet shows B & E which is wrong

The Category & Type columns of this sheet should show the Category of the Removed Products (A & E) in Month. As A is not present anymore in Month1(Month=4) and also this is the maximum month we can select so both the Category & Type should show SOLD. If there had been a Month=5 or 6 etc... then the removed product should be looked up into the table where  Month is maximum and the Category & Type present there should be reported here(in case of E).

If the removed product is not there in the maximum possible Month then it should show SOLD for both Category & Type Columns(in case of A)

I tried taking a cross join of the two tables INPUT & ISLAND in the original data but due to record abundance it failed. I did this to make all possible combinations of Month & Month2 etc.

Sir, can we change something in the script to make it happen.

Regards

[swuehl]

qlik_techie,

that's strange. I do see your requested outcome on my site:

I am using QV11, but I think I haven't used a QV11 only feature. So I checked on latest QV 10 SR5 (released last week), and it seems also ok.

I have seen the issues you described on an older version of QV10 (SR3), though.

What version are you using? If not on QV10SR5, could you upgrade to the latest SR or to QV11?

Regards,

Stefan

[#9]

Dear Sir,

In fact i'm using QV 10 SR3.As you've said...thats why this problem.

Why is there an issue with this version Sir ?

Could you please add the latest qvw file showing NEW & OLD also.

I'm going to try it on QV 11

Anyways, the solution is indeed what i was looking for..BRILLIANT....BRILLIANT....BRILLIANT

THANKS........THANKS..........THANKS.........

Regards

[swuehl]

That's the version I already attached yesterday 'Compare QUES_2.qvw'.

You should see the correct results when opening in QV10 SR5 or QV 11.

Not sure why there is an issue with older versions. They seem to have fixed it now.

Have a nice weekend.

Stefan

[#11]

Dear Sir,

I just installed QV 11 and i can see the correct result.

But between all this, i forgot to ask you the working of the expression:

if(Common,only({<Product /= p(Product2),Category=,Type=,Month=p(Month2) >} Category))

Thanks & Regards