Reply
Yellow Belt
Posts: 21
Registered: ‎08-31-2016
Accepted Solution

Two Source, Date Operator + Join Question

Hello all,

 

I have Salesforce data (Cases) and JIRA Data (Issues). I'm trying to create a dataset to power a card which simply shows Salesforce Cases created  vs JIRA Issues created (grouped by whatever time period I choose, day, month, year, etc).

 

To join the data the like field I need to use is CreatedDate (Salesforce) and Created (JIRA), both are Date/Time operators. (See attached Screenshot Domo 4).

 

When making the card it only correctly counts the Salesforce cases if in the Date Range Field I choose the date/time field that is from Salesforce which makes the JIRA ones wrong, and the only way to show the correct count of JIRA issues is to use the JIRA date field which then makes the Salesforce ones wrong.

 

 

 

Thoughts?


Accepted Solutions
Solution
Accepted by topic author TravisSimpson
‎05-18-2017 02:45 PM
AS Blue Belt
Blue Belt
Posts: 330
Registered: ‎09-08-2016

Re: Two Source, Date Operator + Join Question

Hi Travis!

 

Since you're joining the two datasets on the date column and SFDC is on the left side, for every row of SFDC there probably isn't a corresponding exact datetime match of JIRA data. And if you switch it around there probably isn't a corresponding match in SFDC for JIRA rows, so you're only getting JIRA.  Typically joins would be done on a distinct row id of some kind, like an issue or a case number, like you're trying to find more informative data in JIRA that SFDC doesn't have.  Like a transaction line item gets joined to a transaction header.  Or an address gets joined to a customer.

 

Your datasets are probably mostly unrelated, so what you should do instead is append rows together instead of joining rows, and create a new column that describes an entry as either JIRA or SFDC.  That way every row of data has a source label and a date, so you can create a card that groups on date but displays what source the case or issue came from.

 

You might have to reorder, remove, or rename columns as part of that process, since the two inputs would have to have the same structure in order to append them together.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Solution
Accepted by topic author TravisSimpson
‎05-18-2017 02:45 PM
AS Blue Belt
Blue Belt
Posts: 330
Registered: ‎09-08-2016

Re: Two Source, Date Operator + Join Question

Hi Travis!

 

Since you're joining the two datasets on the date column and SFDC is on the left side, for every row of SFDC there probably isn't a corresponding exact datetime match of JIRA data. And if you switch it around there probably isn't a corresponding match in SFDC for JIRA rows, so you're only getting JIRA.  Typically joins would be done on a distinct row id of some kind, like an issue or a case number, like you're trying to find more informative data in JIRA that SFDC doesn't have.  Like a transaction line item gets joined to a transaction header.  Or an address gets joined to a customer.

 

Your datasets are probably mostly unrelated, so what you should do instead is append rows together instead of joining rows, and create a new column that describes an entry as either JIRA or SFDC.  That way every row of data has a source label and a date, so you can create a card that groups on date but displays what source the case or issue came from.

 

You might have to reorder, remove, or rename columns as part of that process, since the two inputs would have to have the same structure in order to append them together.

Aaron
MajorDomo @ Merit Medical

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Yellow Belt
Posts: 21
Registered: ‎08-31-2016

Re: Two Source, Date Operator + Join Question

Thanks for the tip, got it going via the ETL attached

Highlighted
White Belt
Posts: 14
Registered: ‎08-08-2016

Re: Two Source, Date Operator + Join Question

that seems really messy to append together?  Of all the items in those two tables you only have a date field in common?  Sorry to jump in late on this but why would you join these two together if they aren't common at all?

 

Seems to me if they are cases you should have something more similar like the name of the company?  or maybe a ticket?  or some combination of the two?  

 

I would try to scrub the data, maybe add some sort of identifier column, or filters to see if you can match the data up together.  Worst case scenario is pull in another piece of data to use as a bridge to link them all together.   I'd play around more before I just tossed them in an append.  Good luck

Yellow Belt
Posts: 21
Registered: ‎08-31-2016

Re: Two Source, Date Operator + Join Question

My goal was to just display the total cases created by month and the total JIRA issues created by month on the same chart so this seemed like the quickest way to do it.

 

We don't necessarilly have many data ties between the system, the two are integrated but it's done through a 3rd party addon for JIRA so no data is stored on the Salesforce side, it's 2 visualforce pages on a page layout giving a peak at the data that's stored in JIRA.

 

Many things are done in Salesforce that have no relation to JIRA, many things are done in JIRA with no relation to Salesforce and a subset is linked between the two with the 3rd party connector. 

 

So to your point some issues in JIRA will have an account name stored in a field that is mapped from Salesforce but only the ones that were created from Salesforce and inserted into JIRA as account stuff is only stored in Salesforce. I might have to venture down a route you mentioned if I was offering more in depth views for analysis or drilldown but at just a running totals per month this seems to work.

Announcements
Customize your Dojo Handle! You can update your Dojo screen name by clicking on My Settings. click here! Thanks!