I have three datasets:
- CRM(Enquiry Id Enquiry Date Enquiry Time Patient Name Gender Age Location Treatment Name Type Source Status Assigned To Comments Payment Mode Followup Date)
- Facebook (Ad Set Name Treatment Location Ad name Result type Results Reach Impressions Cost per result Amount spent (INR) Clicks (all) CTR (all) CPC (All) Reporting starts Reporting ends)
- Finance (Enquiry ID Date of Completion Date of Surgery Hospital Name Month Patient Name Treatment BD Name MODE Dr Name Model EMI Partner Amount Collected by City OP/IP Total Surgery Cost Insurance Approved Amount Hospital Cash Collection Mykare Collection Final Bill Amount)
I’ve joined the CRM and Finance data using ‘Enquiry ID,’ and I’ve joined CRM with Facebook using ‘Location’ because there are no unique values in the Facebook data. However, when I attempt to create a value, the sum doesn’t come out as expected.
Need a result like this
But I am getting this
Any assistance or guidance would be appreciated.
can you give us an example how your data looks like after the joins?
If I understand your description correct, you have a list of unique IDs after the first join. And the Facebook data is per location.
So if you join on the location, you add the spent amount every time the location matches.
You would need to aggregate on the location after the first join, so you dont duplicate the facebook data.
After the join with location, values are duplicating. below are the sample data
as you can see you have now many duplicated rows. Can you extend you join condition?
Hi @ErikG ,
Those duplicates because of join using ‘location’ field due to the absence of unique values in the Facebook data.
Are you using CSV or database?
Maybe you can use AVG instead of SUM. There you should get a little closer to the proper values.
You also could try to create a new field with
Hello @Fatima, you could try to change the join type and see if that helps. From the CRM dataset left join the Facebook dataset using the location field. This might help eliminate the duplicates when you join the data. I think the best solution though is to add a unique value to the Facebook dataset that will match the CRM dataset, otherwise I can’t guarantee that join will work the way you want.