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