4 Replies Latest reply: Sep 21, 2017 2:03 AM by Shahid Mr RSS

    Multiple mapping of Same Key with other Table

    Shahid Mr

      Let me put the scenario first to simplify the question. I have a table called Person  , Now in our business logic Same person can be a customer as well as seller in different jobs. So , I have a fact table "Order" where there are two columns , CustomerID and SellerID .  Now when i am associating these tables . Table Person and Table Order . It allows me to make only one association which is Order.CustomerID and Person.PersonKey . I want to use Person.PersonKey to associate with Order.SellerID  as well.

      One Solution is to separate person table according to the roles . But if i follow this approach , I'll have to make a whole lot of tables to take care of similar issues.

      This approach is feasible and valid in terms of Star Schema but looks like i am not able to do it in QlikSense

      Is there any better way of doing it?

        • Re: Multiple mapping of Same Key with other Table
          youssef belloum

          Hi,

           

          I'm not sure i understand at 100% your problem, but, let me try,

           

          the key field connecting your table "order" to your table "person" may be "CustomerID" and also may be "SellerID" ?

           

          If it is the case, you can try a composite Key, by concatenating these two fields like this:

           

          CustomerID &'-'& SellerID as PersonKey (on the order table)

           

          and do the same on the Person table.

           

          let me know

           

          Regards,

          Youssef

          • Re: Multiple mapping of Same Key with other Table
            ishan Bhatt

            Hi Sahid,

             

            I Think you want to connect Person and Seller table to the fact table. If it is right the try below logic.

             

            Table1:

            LOAD

                Person_ID

                PersonName

                Xyx.

                YTS

            FROM [lib://Data/Data.xlsx]

            (ooxml, embedded labels, table is [Table 1]);

             

             

            _MappingTable:

            Mapping Load

            "PersonID",

            PersonName

             

            Resident

            Table1;

             

             

            Table2:

             

            LOAD

                "PersonID",

                "SellerID",

                Applymap('_MappingTable',"PersonID",'NA') AS PersonName,

                Applymap('_MappingTable',"SellerID",'NA') AS SellerName

            FROM [lib://Data/Data.xlsx]

            (ooxml, embedded labels, table is [Table 2]);

             

             

            Exit script;

             

             

            Hopefully, this will helpfull to you.

             

            Ishan