topic Re: Table association issue with customers & payments tables in App Development

Table association issue with customers & payments tables

Sat, 16 Jan 2016 20:06:30 GMT

Hi guys

I'm experiencing with Qlik Sense for the last week I have successfully created many applications but I'm having troubles to generate the right table association in the following scenario:

I've created 2 tables, 'customers' and 'payments'.

The final goal is to create a report that describes the money transfers between countries.

For that purpose, 'payments.sender_id' and 'payments.receiver_id' should both be connected to 'customers.cust_id'.

Qlik Sense seems to have a very hard time creating the right associations in this scenario.

I tried a lot of works-around and the only one that worked till now was to duplicate the 'customers' table so one copy will be connected to the sender and the another to the receiver.

Unfortunately this work-around means that if I want to use other customers' data tables, they should also be duplicated.

Can anyone thinks on a more straightforward solution?

Thanks 🙂

customers

cust_id	country
1	USA
2	UK
3	France

payments

payment_id	sender_id	receiver_id	amount_usd	payment_date
999991	1	2	34.87	2016-01-02
999992	1	3	1.76	2016-01-02
999993	3	2	45.45	2016-01-07

Re: Table association issue with customers & payments tables

swuehl — Sun, 17 Jan 2016 01:58:27 GMT

Another option is to transform the sender_id and receiver_id to a cust_id and transactor_type form:

customers:

LOAD cust_id, country

FROM TableCustomers;

payments:

LOAD payment_id, amount_usd, payment_date

FROM TablePayments;

Link:

LOAD payment_id, sender_id as cust_id, 'sender' as transactor_type

FROM TablePayments;

LOAD payment_id, receiver_id as cust_id, 'receiver' as transactor_type

FROM TablePayments;

Re: Table association issue with customers & payments tables

Sun, 17 Jan 2016 18:01:57 GMT

Thanks for the suggestion, this solution does work for the loading.

However, using that method creates a data model where the customer information of the sender and the receiver are not in a single row, which makes the requested report generation much more complected.

Re: Table association issue with customers & payments tables

swuehl — Sun, 17 Jan 2016 18:58:02 GMT

you can access the sender / receiver information (for example in a straight table with dimension payment_id) like

=Only({<transactor_type = {'sender'}>} cust_id)

=Only({<transactor_type = {'receiver'}>} cust_id)

Re: Table association issue with customers & payments tables

vinay_hg — Mon, 18 Jan 2016 05:28:34 GMT

Easy way use applymap, bring required customer table details to payment table.. then it will be single table easy to use..

Note: check if size of data is too huge, then it may take bit more time then join while processing.

Re: Table association issue with customers & payments tables

Mon, 18 Jan 2016 08:29:47 GMT

LOAD

cust_id,

cust_id AS sender_id,

cust_id AS receiver_id,

country

FROM customers;

Keeps single record in your Customers table, but will also link to Sender/ Receiver tables.

Re: Table association issue with customers & payments tables

Mon, 18 Jan 2016 15:17:41 GMT

Thanks but this won’t work.

We can associate the tables based on ‘sender_id’ or ‘receiver_id’ and get the ‘customers’ information for one or the other, but not for both of them.

Associate the tables based on the combination of both columns has no analytic meaning.

Re: Table association issue with customers & payments tables

Mon, 18 Jan 2016 16:46:49 GMT

apologies - attempted to multi-task and failed.

Duplicating the Customer table would be your best bet. you could do it after you have loaded all the data in though, so you only have to load it once.

LOAD

cust_id AS sender_id,

country AS sender_country

RESIDENT Customers;

LOAD

cust_id AS receiver_id,

country AS receiver_country

RESIDENT Customers;

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 07:18:23 GMT

Thanks, this does takes us a step forward.

The question now, is if we can use the “Only” measures (which are the sender’s and receiver’s countries) as dimensions for the next step (the goal was to create a report which summarize transactions between countries).

If for that purpose we must prepare the data in advance, in the loading phase, it quite takes the edge off of using QlikSense as an exploration tool.

What do you say?

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 08:05:48 GMT

We are talking here about a very simple use case.

In general, this solution requires duplication of all tables containing the ‘cust_id’ column.

Quite a burden when we’re talking on a large number of tables.

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 10:29:11 GMT

This is a nice solution for my simplified example, however I find it very limited for real-life use cases.

We have to create the extended payments table in advance therefore knowing in advance how we want to use the data.

This is an issue for exploration tool.

MAPPING relates only a single value to a key.

If we would like to add not just the sender’s and receiver’s countries to the payments table but also other attributes such as the customers’ types or genders, it would requires defining multiple maps, one per attribute which makes this solution quite cumbersome.

It would have been great if the following syntax would have been supported… ☺

/* The first column (‘cust_id’) is the key, the rest of the columns are the values */

cust_map:

Mapping Load cust_id,country,cust_type,gender resident cust;

/* Additional argument defines the requested column */

…

ApplyMap('cust_map', sender_id, country , null()) as sender_country,

ApplyMap('cust_map', sender_id, cust_type, null()) as sender_type,

ApplyMap('cust_map', sender_id, gender , null()) as sender_gender,

ApplyMap('cust_map', receiver_id, country , null()) as receiver_country,

ApplyMap('cust_map', receiver_id, cust_type, null()) as receiver_type,

ApplyMap('cust_map', receiver_id, gender , null()) as receiver_gender,

…

If the customers table holds the country code (instead of the country name like in my simplified example) this solution becomes pretty complicated.

map_country_code_to_country_name:

Mapping Load country_code,country_name resident cntry;

map_cust_id_to_country_code:

Mapping Load cust_id,country_code resident cust;

…

ApplyMap('map_country_code_to_country_name',ApplyMap('map_cust_id_to_country_code',sender_id, null()),null()) as sender_country,

ApplyMap('map_country_code_to_country_name',ApplyMap('map_cust_id_to_country_code',receiver_id, null()),null()) as receiver_country

…

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 11:26:16 GMT

could you prepare your customer tables in your load script, then duplicate the result?

e.g.

CustT1:

LOAD * FROM Cust_Table1;

INNER JOIN (CustT1)

LOAD * FROM Cust_Table2;

DROP Cust_Table2;

INNER JOIN (CustT1)

LOAD * FROM Cust_Table3;

DROP Cust_Table3;

etc.

Sender_Customers:

LOAD *,

cust_id as Sender_ID

RESIDENT CustT1;

Receiver_Customers:

LOAD *

cust_id as Receiver_ID

RESIDENT CustT1;

so you load all the different customer tables once.

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 15:40:19 GMT

It can only work if there's a relation of 1:1 between all customer's tables, which is rarely the use case.

Imagine for example what would be the result set if two of the tables would be cust_address, and cust_phone where each customer might have multiple records in each table.

Re: Table association issue with customers & payments tables

swuehl — Wed, 20 Jan 2016 18:36:31 GMT

You can create two calculated dimensions:

=Aggr( Only({<transactor_type = {'sender'}>} cust_id), payment_id)

=Aggr( Only({<transactor_type = {'receiver'}>} cust_id), payment_id)

And expressions like

=Only(payment_date)

=Sum(amount_usd)

to analyze the country transactions (yes, you can also reference the country field, since it is linked to cust_id:

=Aggr( Only({<transactor_type = {'sender'}>}country), payment_id)

=Aggr( Only({<transactor_type = {'receiver'}>}country), payment_id)

)

Re: Table association issue with customers & payments tables

Wed, 20 Jan 2016 20:59:45 GMT

Loved it.

I think there's an opportunity here for Qlik to enhance the support for the use case I have presented, but given our current options - this is a great solution.

The modifications required in the loading phase are minimal, the data model is simple and efficient and the user has all the options open in the reporting phase.

Chapeau!

Dudu