Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
cust_id | country |
---|---|
1 | USA |
2 | UK |
3 | France |
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 |
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
…
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.
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.
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)
)
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