Table association issue with customers & payments tables

[#1]

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

[swuehl]

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;

[#3]

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.

[swuehl]

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)

[vinay_hg]

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.

[#6]

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.

[#7]

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.

[#8]

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;

[#9]

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?

[#10]

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.