14 Replies Latest reply: Jan 20, 2016 4:03 PM by Dudu Markovitz RSS

    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
        • Re: Table association issue with customers & payments tables
          Stefan Wühl

          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
            vinaykumar 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.

              • Re: Table association issue with customers & payments tables

                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

              • Re: Table association issue with customers & payments tables
                Andrew Mein

                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.