Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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
1 Solution

Accepted Solutions
MVP
MVP

Re: Table association issue with customers & payments tables

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)

)

14 Replies
MVP
MVP

Re: Table association issue with customers & payments tables

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;

Not applicable

Re: Table association issue with customers & payments tables

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.


MVP
MVP

Re: Table association issue with customers & payments tables

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
Contributor II

Re: Table association issue with customers & payments tables

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.

Not applicable

Re: Table association issue with customers & payments tables

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.

Not applicable

Re: Table association issue with customers & payments tables

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.

Not applicable

Re: Table association issue with customers & payments tables

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;

Not applicable

Re: Table association issue with customers & payments tables

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?

Not applicable

Re: Table association issue with customers & payments tables

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.