Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
wcilliers
Partner - Creator
Partner - Creator

Lookup() Chart Function

Hi All,

I am in need of a solution similar to the lookup()script function, but for charts. Pretty much like Excel VLookup.

I have a Sales Fact table and two dimension tables, Customers and Products.

I also have a special pricing table for customers on each product. I want to be able to display the special price for each product by customer, obviously also against sales and other measures.

I have tried using (Only) but this doesn't work unless only one product and customer is selected. I also though of doing an ApplyMap() on the Fact table creating a key from Customer&Product Codes. But this loses the visibility on special pricing for products that has never been sold for that customer.

Any ideas would help please?

DataModel.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks,

Wynand

1 Reply
cheenu_janakira
Creator III
Creator III

Hi Wynand,

It's difficult to picture without a concrete example, but would something of this kind work?

Create 2 new columns which are exact replicas of CardCode inside the SpecialPricing and Customers tables, so that you have 2 additional fields in both tables, e.g. CardCode_SP and CardCode_Cust.

Then use these fields as "pseudo-filter" with set analysis, e.g.

SUM({$<CardCode_SP = {$(=CHR(39) & Concat(DISTINCT CardCode, CHR(39) & ',' & CHR(39))&CHR(39))}, CardCode=>} Blahblah)

In this case there won't be any data reduction due to selections on CardCode field, but you are parsing the values selected in CardCode and putting them through to CardCode_SP as a pseudo-selection.

Hope this makes sense and might lead you in the right direction.

Otherwise, a concrete sample file/example would be great for debugging.

KR,

Cheenu