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

[sunny_talwar · Accepted solution]

Try this may be

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

[davidcollins_expert · Accepted solution]

Hi Sunny,

Incredible, this works!

Can you explain what it means to put TOTAL <ITEMID> in front of the set modifier?

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

Thanks.

David

[sunny_talwar · Accepted solution]

Check this out

Defining the aggregation scope 

[davidcollins_expert · Accepted solution]

Hi Sunny,

Thanks for your quick reply, I checked it out and understand now that there's a difference between changing (1) the aggregation scope for selections and (2) the aggregation scope for dimensions.

(1) is applicable through set analysis -> {}

(2) is applicable through TOTAL

You exclude all dimensions from the aggregation except <ITEMID>. Thank you so much. Have a nice Christmas.

David 

[davidcollins_expert · Accepted solution]

Hi Sunny,

I cannot get it working with the variable exp_KEY_CUST_ITEM. My Set Analysis looks ok to me. Updated qvw.

max(TOTAL <ITEMID> {<%KEY_CUST_ITEMID={'=$(exp_KEY_CUST_ITEM)'},FACTTYPE={'NetPrice'}>} NETPRICE)

Thank you for your help.

Rgds,

David

[sunny_talwar · Accepted solution]

There are multiple ITEM_IDs which are possible using your condition... which ITEM_ID are you looking to display?

[sunny_talwar · Accepted solution]

If it is all, then why not just this

Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE)

[davidcollins_expert · Accepted solution]

Hi Sunny,

Yes thank you. The user already selects the customer (Aggregation scope by selection) and you excluded ItemId from TOTAL (Aggregation scope by dimension). So you're right you can be sufficient by

{<FACTTYPE={'NetPrice'}>}

Sorry to bother you with one last question. I inserted a calculated dimension in order to show only FACTTYPE = 'Products'.

=if(FACTTYPE='Products',ITEMID,null())

and I selected Suppress when value is null. 

Now my formula in column NETPRICE_NextTo doesn't work anymore. Should I leave FACTTYPE = 'NVP' in the straight table, but make these rows somehow invisible?

The purpose is that the user sees a price list with gross and net prices in two columns one besides the other, but only for the rows with 'Product' in FACTTYPE.

Rgds,

David

[sunny_talwar · Accepted solution]

Instead of using a calculated dimension... try this expression with ITEM_ID as dimension

If(Only({1} FACTTYPE) = 'Products', Max(TOTAL <ITEMID> {<FACTTYPE={'NetPrice'}>} NETPRICE))