Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Table association issue with customers & payments tables

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_idcountry
1USA
2UK
3France

  • payments
payment_idsender_idreceiver_idamount_usdpayment_date
9999911234.872016-01-02
999992131.762016-01-02
9999933245.452016-01-07
14 Replies
Not applicable
Author

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

1.

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.

2.

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,

3.

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

Not applicable
Author

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.

Not applicable
Author

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.

swuehl
MVP
MVP

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)

)

Not applicable
Author

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