Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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,
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
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