Multiple mapping of Same Key with other Table

[compassadm]

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?

[YoussefBelloum]

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

[compassadm]

Hello Youssef ,

Thanks for the reply!

You see the problem is they need to be separate so they can be individually refer to the Person's Table . If i make the composite key how can i find the name of customer and seller? Composite key cannot solve this,

[ishanbhatt]

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

[compassadm]

Hi Ishan ,

This is super!

Yes , i just implemented this logic by the use of Applymap() function and this happens to be the exact copy so thanks  for verifying it

Regards,
Shahid