topic Re: Intersection of List box values in QlikView

Intersection of List box values

Fri, 29 Jun 2012 06:46:10 GMT

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

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 08:33:59 GMT

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

Re: Intersection of List box values

Fri, 29 Jun 2012 09:25:01 GMT

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

Re: Intersection of List box values

Fri, 29 Jun 2012 09:29:31 GMT

Sir,

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

Regards

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 09:39:50 GMT

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

Re: Intersection of List box values

Fri, 29 Jun 2012 09:50:05 GMT

Thank you so much Sir.

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

Regards

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 10:02:11 GMT

Your welcome.

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

Re: Intersection of List box values

Fri, 29 Jun 2012 10:32:44 GMT

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

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 10:54:31 GMT

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

Re: Intersection of List box values

Fri, 29 Jun 2012 11:50:40 GMT

Hello Sir,

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

Please have a look.

Regards

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 12:01:37 GMT

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?

Re: Intersection of List box values

Fri, 29 Jun 2012 12:43:28 GMT

Dear Sir,

Please find the file attached.

Regards

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 13:10:07 GMT

This is a little more tricky, please check attached.

Re: Intersection of List box values

Fri, 29 Jun 2012 16:40:46 GMT

Dear Sir,

Thanks for you efforts for me.

Please see the file attached & the remarks over there.

Regards

Re: Intersection of List box values

swuehl — Fri, 29 Jun 2012 21:44:14 GMT

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

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...

Re: Intersection of List box values

Sat, 30 Jun 2012 02:00:21 GMT

Dear Sir,

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."

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

Re: Intersection of List box values

swuehl — Sat, 30 Jun 2012 11:25:23 GMT

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

Re: Intersection of List box values

Sat, 30 Jun 2012 12:46:22 GMT

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

Re: Intersection of List box values

swuehl — Sat, 30 Jun 2012 13:17:36 GMT

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

Re: Intersection of List box values

Sat, 30 Jun 2012 13:57:34 GMT

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