Handling tables with multiple fields in common?

[Anonymous]

Hello all,

I am pretty new to the Qlik community, hence I do apologize if this question has been answered earlier in some form or the other - I was not able to find any direct answers in my searches. I am working on building a QlikSense web application for my company that helps in identifying those customers who may end up not being our customers by the end of a quarter, i.e., they run the risk of becoming a “lost customer” to us. I wish to map the potential revenue loss from these customers (calculated as the trailing twelve months revenue from said customers), with the design-in opportunities, if any, available for those companies in our sales funnel database.

The primary data for this application comes from two tables – one of them is the revenue table, and the other is the sales funnel table.

1. The revenue table has (amongst others) the following fields that I am interested in – UNIQUE_ID, CUSTOMER, QUARTER, DIVISION, BUSINESS_UNIT, FAMILY, PART_NUM, DOLLARS.

1. The funnel table has the fields: OPPORTUNITY_ID, CUSTOMER, DIVISION, BUSINESS_UNIT, FAMILY, PART_NUM, OPP_DATE, DESIGN_IN_DATE, DESIGN_WIN_DATE, FUNNEL_STATUS, OPPORTUNITY_VALUE

For the funnel table, I also created a custom field in the load script called FUNNEL_STALENESS, which essentially tracks how old an opportunity is. For example, if the maximum value of the three dates in the table - OPP_DATE, DESIGN_IN_DATE, DESIGN_WIN_DATE is over 270 days old from today, then that particular opportunity is considered ‘stale’, else it is ‘on track’. I used the following expression in the load script to accomplish that: IF(Today() - RangeMax(OPP_DATE, DESIGN_IN_DATE, DESIGN_WIN_DATE) > 270, ‘Stale’, ‘On track’) AS FUNNEL_STALENESS

My question is: how do I join these two tables in the load script, given that there is no common key between the two of them, and multiple fields in common? Right now, I use the link table method to load these tables so I can avoid synthetic keys. I currently use a combination of CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY-PART_NUM to try and link the two tables.

The application has a simple table that looks a bit like this, now:

Lost customers	TTM Dollars	Funnel Opportunity	Funnel status	Staleness
ABCD	$0	$500,000	ACTIVE	Stale
ABCD	$0	$200,000	DESIGN IN	On track
ABCD	$1,000	$0	-	-

The error that I get is that the application definitely treats the two source tables as two different tables. If I try to filter the table to reflect only customers who are "On track", I get no rows selected. The ideal scenario would be to get a table that looks like the one below:

Lost customers	TTM Dollars	Funnel Opportunity	Funnel status	Staleness
ABCD	$1,000	$500,000	ACTIVE	Stale
ABCD	$1,000	$200,000	DESIGN IN	On track

I get the feeling this is happening because of the way CONCATENATE or Link tables work, mainly because there are combinations of CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY-PART_NUM that are there in the revenue table, but not there in the sales funnel table.

Is there a way for me to kind of LEFT JOIN the two tables (revenue table being the left table) so that for every possible combination of CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY_PART_NUM, so that if such a combination exists in the revenue table, then it maps to the relevant opportunities in the funnel table and creates multiple lines for those multiple opportunities?

Thanks for your help in advance.

[vamsee]

Hi Karthik,

Please correct if wrong, from your requirement I have understood that you do all your analysis based on the funnel table and the Revenue table is only used to fetch TTM Dollars.

So try the following.

Step 1: Aggregate your Revenue Table to Key, Dollars level. (Since you mentioned you have other columns which your not interested in). at CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY_PART_NUM level.

Step2: Do not try to Join use ApplyMap() instead for the dollar values. Don't join - use Applymap instead

               CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY_PART_NUM as Key,

               Dollars as Value.

Step 3: ApplyMap the dollar value onto funnel table.

-----

Coming to your second point that you have more combinations in Revenue table but are missing in Funnel Table then do it the other way around i.e. create 3 Maps using funnel table ( Staleness, FunnelStatus, Funnel Opportunity) and then applymap to revenue table.

Note: Joins might create duplicate records but ApplyMap does not.

[Anonymous]

Hi Vamsee,

Thanks for the reply. I think that it would suit my purpose well if I try to use ApplyMap to map the relevant funnel table values to the revenue table. I need the revenue table and all the associated columns since there are other work pages in the app that I have created, which are entirely dependent on the revenue table.

I do have a follow-up question though. The funnel table has a primary key in OPPORTUNITY_ID field. So, for the same combination of CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY_PART_NUM, there exist multiple opportunities with unique OPPORTUNITY_ID values, multiple values for the FUNNEL_STATUS field (could be one of OPPORTUNITY, DESIGN IN and DESIGN WIN), corresponding values for the respective date fields and the opportunity value. Would ApplyMap still work in such a situation?

I'd have to look at creating a map for the following situations, where the key remains CUSTOMER-DIVISION-BUSINESS_UNIT-FAMILY_PART_NUM:

1. OPPORTUNITY_VALUE where FUNNEL_STATUS = 'OPPORTUNITY'

2. OPPORTUNITY_VALUE where FUNNEL_STATUS = 'DESIGN IN'

3. OPPORTUNITY_VALUE where FUNNEL_STATUS = 'DESIGN WIN'

4. "insert relevant expression for RangeMax(multiple dates) to evaluate FUNNEL_STALENESS"

I am not too sure if this would work, or map correctly, since I am dealing with a many-to-many mapping between the two tables.

As always, I appreciate and thank you for your help.

Karthik.

[vamsee]

Hi Karthik,

I am not pretty sure how to handle that until I look at your data. On a general approach try loading your revenue data twice, once for the applymaps and load it again to join it.

Applymap only fetches the first record it finds in the source table. The first record being fetched depends on the sort order of the source table. If you have a one to many relationship between the Key Value pair then appymap will not be ideal.

For this Keep or Join or Use Concat() for the Values and then later subfield.

Hope I could be of help. Thank you.

Regards

Vamsee