Vlookup in Straight Table as opposed to ApplyMap

[davidcollins_expert]

Hi QV Community,

I have a problem looking up prices against products. Maybe you can help me out? I have made a small reduction of the problem into a qvw.

I have two tables: Products and Netprices. For now I have concatenated both tables. A solution with qualified unconnected tables is also allowed.

For FACTTYPE = 'NetPrices' I have a CUST_NUM and ITEMID.

For FACTTYPE= 'Products' I only have ITEMID. The CUST_NUM for those rows should be retrieved from a variable vCustomer. This variable gets updated each time the user selects another customer in ListBox CUST_NUM.

The reason why I do it like this is that I try to avoid a composite key. My data is about 120000 lines of products and 12000 customers. I avoid creating CUST_NUM&'_'&ITEMID with a left join on the Products table. This would blow up hugely my data. Therefore I'm looking for a solution where the user selects a Customer. This selection is then stored into vCustomer and on that basis I would like to search for combination $(vCustomer)&'_'&ITEMID.

I thought about storing this combination into another variable, namely exp_CUST_ITEM, in order to use that variable in Set Analysis. I already succeeded retrieving that variable in the straight table through column exp_CUST_ITEM.

My problem occurs when I try to put the Netprice next to the GrossPrice (column NetPrice_NextTo) for the rows where FACTTYPE = 'Products'. At the end of the day in my App only rows for which FACTTYPE = 'Products' will be shown.

I have tried with the following Set Analysis:

max({<%KEY_CUST_ITEMID={'1108_6672014'},FACTTYPE={'NetPrice'}>} NETPRICE)

It doesn't work. I have used literals in order to be sure there's nothing wrong with the punctuation of Set Analysis. I think this should be possible because we also do it for Budget against Actuals. There we also have Budget data concatenated to Actuals data and we show it side by side. The difference is that for Budget / Actuals calculations we have the dimension in the script. We now do not have CUST_NUM on the rows of FACTTYPE = 'Products', but we have the variable vCustomer.

The Set Analysis formula above  shows the NetPrice besides the rows of FACTTYPE NetPrice, but not on the rows of FACTTYPE = 'Products'. Maybe you know how to do this, if it can be achieved? Attached qvw.

Thank you for your cooperation.

David

[davidcollins_expert]

Hi Sunny,

Great solution! Thank you so much. Can you explain why:

1) if(FACTTYPE='Products',expression) doesn't work?

2) if(Only(FACTTYPE)='Products',expression) doesn't work?

3) if(Only({1}FACTTYPE)='Products',expression) does work? Why do you use {1}?

4) Is there still need to put CUST_NUM into a variable vCustomer? I don't think so. We don't use %KEY_CUST_ITEM at all.

Some further elaboration on the App. I added FACTTYPE = 'Discounts_Cust'. These are discounts based on LINEDISC_PROD. They also should be picked up (vlookup in the GUI) on the rows with FACTTYPE = 'Products'. 

So I applied the same logic:

Max(TOTAL <LINEDISC_PROD> {<FACTTYPE={'Discounts_Cust'}>} PERCENT1)

Now the dimension aggregation is on LINEDISC_PROD instead of ITEMID.

When I apply this code (Straight Table B.) you can see that for FACTTYPE = 'Discounts_Cust' , both customers are appearing: the selected one and the other customer. I think this is caused by {1}. It should only show the selected customer.

When I then apply also your if(only(FACTTYPE)='Products' on the above line of code (Straight Table C.) everything seems ok. Should I be worried or is it ok?

Attached update version of the qvw. 

Rgds,

David

[sunny_talwar · Accepted solution]

1) if(FACTTYPE='Products',expression) doesn't work?
2) if(Only(FACTTYPE)='Products',expression) doesn't work?

These two are the same expressions.... they don't work because when you select a CUST_NUM, FACTTYPE = 'Product' is out of selection

So, in order to make sure we see Products, we need to ignore selection in either CUST_NUM or everything.

3) if(Only({1}FACTTYPE)='Products',expression) does work? Why do you use {1}?

{1} just means that ignore selection in all fields. This might work most of the times since it is an if condition and your actual value is driven of of the expression. But there might be specific cases where you might need to create a more sophisticated set analysis.

4) Is there still need to put CUST_NUM into a variable vCustomer? I don't think so. We don't use %KEY_CUST_ITEM at all.

I think you are right

When I then apply also your if(only(FACTTYPE)='Products' on the above line of code (Straight Table C.) everything seems ok. Should I be worried or is it ok?

As long as you get your desired output based on multiple tests where you select and un-select different selections that your users will make, I won't be to worried. The solution to a problem can have multiple solution, but they all might give different results on your interaction with the selections you make. Once you see an issue, you can fine tune your expressions. There is no easy way to tell that your current expression will work 100% of the times because we might not have covered all different scenarios here.

[davidcollins_expert]

Hi Sunny,

Everything ok, thank you so much for your input, but still running into some problems. I read about nested set analysis and P() function. Maybe that's the solution for this?

Problem 1

When selecting a customer, the association is made with the product rows for which a net price exists. Association should be made with all product rows, because the user sees a price list and depending on the availability of net price per customer, the net price will be shown or if no net price is available, it will be calculated based on gross price (field in products) less the discount rate. I show you below in printscreen.

When a user now selects the ItemId and he selects a grey ItemId, he switches to another customer. This is not desirable. The user should stay on the same chosen customer. He wants to select for example a category of products and should get the prices for this category, independent of the fact if specific net prices exist for the chosen customer. If the net price doesn't exist, it will be calculated from the gross price less percent1.

Problem 2

In Straight Table A. you see the result of PERCENT1 for both customers. You see that customer 1027 has BREF01 60 and BETEC01 80 as PERCENT1 (discount). Straight Table B. contains the code 

If(Only({<CUST_NUM=>} FACTTYPE) = 'Products', expression)

but you now see that the percentages of customer 1108 (being BREF01 70 and BETEC01 40 according to table A, original data) in Straight Table B are converted to 60 and 80, because of the max ( TOTAL <ITEMID> formula. It should take into account the customer.

Attached updated qvw.

Rgds,

David

[sunny_talwar]

For Problem 1, I am not sure I understand the issue because when I select ITEMID, I don't see the CUS_NUM getting changed? Which ITEMID did you select when this happened?

For Problem 2, I thought we resolved this issue in Table C, where we used this expression

If(Only({<CUST_NUM=>} FACTTYPE) = 'Products', Max(TOTAL <LINEDISC_PROD> {<FACTTYPE={'Discounts_Cust'}>} PERCENT1))

[davidcollins_expert]

Hi QlikView Community,

Without the  valuable input on TOTAL <ITEMID> and if(only({<CUST_NUM=>}, by Sunny Talwar, I would not have been able to solve this quite challenging puzzle.

Below my final solution:

Create a Customers Table in the script which links as a star model into the concatenates with %KEY_CUSTOMER. Change CUST_NUM into %KEY_CUSTOMER in the Straight Table.
Now a user will see in listbox only one Customer, but in essence when a user selects one Customer in the listbox, two rows in [CUSTOMERS] will be selected: %KEY_CUSTOMER = 1000 and %KEY_CUSTOMER = ‘None’.
Create for the rows with FACTTYPE = ‘Products’ an extra column %KEY_CUSTOMER and store the value ‘None’ on each of those rows.
Now all products will be selected, when selecting a customer, not only the ones with NetPrices.

To recapitulate vlookup in straight table:

1. We want to vlookup net prices in a straight table, next to products and gross prices. Net prices have customer and product. Gross Prices only have a product and no customer. We don’t want to create in the script a composite key for customer & product, because it would result into billions of rows. In such a case you get the complete product list for each customer.
2. We would like to show netprices (FACTTYPE = ‘NetPrices’) next to gross prices (FACTTYPE = ‘Products’).
3. We also want to show discounts next to gross prices which allow calculation of the netprice, in the case no netprice is available in FACTTYPE = ‘NetPrices’. Discounts are specified per customer and LINEDISC_PROD.
4. In order to ‘simulate’ a vlookup we concatenate netprices, discounts and products in one table and we make use of TOTAL <ITEMID>, using the aggregation scope on the dimension. 
5.  You enter the aggregation scope just after the Max and just before the set {}
   Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE)
6. The formula will look for the maximum of NetPrice for the given customer and product (ITEMID) and show this NetPrice next to the GrossPrice.
7. The same formula can be built for net Discounts per customer. Here we need to exclude ITEMID from the selection.
   Max(TOTAL <LINEDISC_PROD> {<ITEMID=,FACTTYPE={'Discounts_Cust'}>} PERCENT1)
8. Now you still see all rows: NetPrices, Discounts_Cust and Products. In order to hide rows NetPrices and Discounts_Cust without losing the functionality of TOTAL <ITEMID>, make use of an if statement around the expression.
   Don’t use an if statement in the dimension with “Suppress when value is null”, because it will break the functioning of the formula TOTAL <ITEMID> or TOTAL <LINEDISC_PROD>.
9. The formula to be used is
   If(Only({<CUST_NUM=>} FACTTYPE) = 'Products', expression). We don’t take into account selections on CUST_NUM, because we want both customers: the one selected and the ‘None’ Customer.
10. Now you have a price list which you can call for each customer, only the products are show with their GrossPrices, NetPrices and Discounts next to the Products (ITEMID). It’s also possible now to select for 1 product or a range of products.

Updated qvw in attachment.

Rgds,

David

[davidcollins_expert]

Hi Sunny,

1. In version Lookup_DC (4), when you select customer 1108, you see 4 products associated (white color). They all 8 products should be associated. Only products with netprices are associated.

2. When you now press Product 3895520 (which is in the grey zone, not associated), what happens? My Customer 1108 gets switched to 1027.

This is what I mean with problem 1. There's no association on FACTTYPE = 'Products' for the customer. I have now solved this by creating an extra row for each existing customer. This row has a %KEY_CUSTOMER = 'None'. In the listbox  you use CUST_NUM which is the same for 'None' and number in %KEY_CUSTOMER.

Thanks again for your valuable input on the previous issues!

Rgds,

David

[sunny_talwar]

Make sense, but I guess the only way to solve this issue is by resolving this in the script by adding additional rows where you connect ITEMID 3895520 (and others) with CUST_NUM 1108 for Products. Alternate is using an Alternative straight, but that might give its own problems.

[sunny_talwar]

So, all the issues are now resolved?

[davidcollins_expert]

Yes

[sunny_talwar]

Awesome!!