topic Re: Straight table condition in QlikView

Straight table condition

Thu, 08 Jan 2015 13:18:39 GMT

Hi Folks,

I am new to QlikView and have been trying to find a solution for a problem with no success. Below is the scenario, hope someone can help or guide me in the right direction.

I have a list box of products and i have created two list boxes from it one of which is in Alternate state, Say Product1 and Product2(Product2 is in Alternate State). I need to select one product from each of the two list boxes and I want a straight table to display two things:

1. List of Companies which have bought both the products(Once this is acheived then the sales-amount and OrderIDs for the two respective products) and

2. Similarly List of companies which have bought Product1 but not Product2.

I need the straight table to display the name of the Company, OrderID and Sales-amount. I am capturing the selection in Product1 into a variable vGroup1 and similarly the second in vGroup2. Once I am successfully able to display the 'right' company names in the straight table then ill go deeper and display the sales-amount for each of the two products and their respective OrderIDs.

I have been able to display the both in a textbox via the following commands:

For point 1 = Concat({<ProductName={'$(vGroup1)'}> * <ProductName={'$(vGroup2)'}>}Customers.CompanyName, ', ')

For point 2 = Concat({<ProductName={'$(vGroup1)'}> - <ProductName={'$(vGroup2)'}>}Customers.CompanyName, ', ')

But i have tried multiple variations(including Enable Conditional) of the same formula to display it in Straight Table but all it displays is Companies which have bought Product1 or at other times some some different combinations.

Really appreciate everyones time and efforts....

Re: Straight table condition

Thu, 08 Jan 2015 13:25:32 GMT

Just a clarification. I need two separate tables, ie one with common companies(Point1) and one which will list companies who have bought Product but not Product2(Point2)

Re: Straight table condition

Anonymous — Thu, 08 Jan 2015 14:02:11 GMT

Hi!

It would be easier for us if you attached a sample qvw

Thanks!

Re: Straight table condition

Thu, 08 Jan 2015 14:22:08 GMT

Sure,

Its the first time i am posting my query, so did not realise it. Here it goes

Re: Straight table condition

Anonymous — Fri, 09 Jan 2015 11:35:23 GMT

Hi,

sorry for delay!

I found a solution for point 2: for point 1... I need more time

Hope this helps you.

Re: Straight table condition

Fri, 09 Jan 2015 11:41:37 GMT

Thanks for your efforts Elena, i have tried using and_mode and realised the solution is very simple with and_mode. But now the problem is that the team does not want the solution using and_mode but with the logic i used earlier and now they want it at OrderId level ie it should display only products which were bought in the same OrderID.

Re: Straight table condition

Fri, 09 Jan 2015 11:43:59 GMT

And thanks for your answer, i am sure if i follow your expression, it will definitely lead me somewhere.

Re: Straight table condition

morganaaron — Fri, 09 Jan 2015 11:56:16 GMT

Hi Muntazir,

It's probably worth looking at element functions P() and E(). I created a similar application that had the same product list in two states, and by choosing products from each list would give the customers that bought only one, only the other or both.

In a straight table in the default state, with your customer as the dimension, the expression would then be:

=Sum({<Customer=P({State1}DropAccountCode)>}Sales)

That would give you all the customers sales values that bought both products and their sales value on the default product. For the other product (in State1) you'll want to do the same but add a second modifier like:

=Sum({<Customer=P({State1}DropAccountCode), Product=P({State1}Product)>}Sales)

Which should give you their sales for the product in State 1.

If you want to expand that to OrderID, add that in as a dimension and run through similar steps as above including it in sets to ensure it's across the same OrderID in both states.

Re: Straight table condition

Fri, 09 Jan 2015 12:18:07 GMT

Hi Aaron,

I did ponder using p() and e() in set analysis but thought it may not work. Now since you've pointed out that it worked in your case, will definitely try the same.

Thanks for your inputs.

Re: Straight table condition

Fri, 09 Jan 2015 13:01:09 GMT

Hi Aaron,

Could you please clarify what 'DropAccountCode' stands for in your expression.

Re: Straight table condition

morganaaron — Fri, 09 Jan 2015 13:02:39 GMT

Oops - apologies, that's just left over from my code - that should read "Customer" or whatever field you're using to identify the dimension!

Re: Straight table condition

Fri, 09 Jan 2015 13:09:55 GMT

Thanks for the prompt reply, i guess i am encountering the same problem i faced with a similar expression. There is a 'red line' under the closing curly braces of State1. In place of State1 i have used the name of the variable which is holding the selection from the first listbox.

Anyways, I appreciate everyones time for the post so far.

Re: Straight table condition

morganaaron — Fri, 09 Jan 2015 13:56:56 GMT

That suggests a syntax error - does it say Expression OK or is it registering there's an issue?

I haven't encountered any errors in mine - happy to look at your code if you want to paste what you're trying to use?

Re: Straight table condition

Fri, 09 Jan 2015 14:12:03 GMT

I am using the following code, where vGroup1 is a variable which is capturing the selection from First Text box

=Sum({<Customers.CompanyName = P({'$(vGroup1)'}Customers.CompanyName)>}OrderSalesAmount)

Re: Straight table condition

kiranmanoharrode — Fri, 09 Jan 2015 14:23:07 GMT

Hi Muntazir,

Search for AND mode implementation in Qlik community. AND mode implementation on List box will be the best practice to solve both points that you have mentioned above.

Here is a helpful link.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/19/and-mode

Regards,

Kiran

Re: Straight table condition

kiranmanoharrode — Fri, 09 Jan 2015 14:24:33 GMT

Try to implement AND mode on Product field.

Re: Straight table condition

morganaaron — Fri, 09 Jan 2015 14:52:26 GMT

I managed to get it to work from your example by using Customers.CompanyName as the dimension and:

=Sum({<Customers.CompanyName = P({State1}Customers.CompanyName)>}OrderSalesAmount)

As the expression. Weird it's not working for you!