possible to join (as in SQL) two datasets in Vocalize? | XM Community
Solved

possible to join (as in SQL) two datasets in Vocalize?

  • 30 December 2018
  • 3 replies
  • 8 views

I am trying to build a vocalize dashboard that will utilize two datasources - a survey in which the response to one question will be a unique ID, and a user-defined dataset that I uploaded as a csv. The choice list of ID's used for the one survey question comes directly from our company's main databases; therefore, the csv dataset that I uploaded to vocalize - which also contains a field with those IDs - will have a match to whichever ID a respondent selects.

Is there a way to do a join, as you would in SQL, so that the two datasets can match up in the creation of the vocalize dashboard? Right now, I am mapping those fields into one using the data source settings for the dashboard, but all that is accomplishing is the vocalize equivalent of a SQL union rather than a join.

The relationship between these two datasets is a one-to-many; for every unique response that will be submitted through the survey, there are several matching records in the csv dataset. My goal in vocalize is to use the record table grid widget to display some of the information from the uploaded csv based on a "where clause" of the survey response ID field is not null (i.e. as survey responses are submitted, dashboard users are seeing the relevant records in the table). But I also want information from other fields of the survey joined into those records as well.

I tried playing around with the idea of uploading the csv dataset as a contact list instead, and using an external data reference validation logic in survey flow to pull data from the list and set it as embedded data. But the one-to-many relationship hindered that plan since the survey only writes one row per response. Although, maybe that's still a possibility and I'm just doing it wrong?

As of right now, merging/mapping fields in vocalize seems like my only option to accomplish this, but that method is producing a 'union' rather than a 'join'. If there is someway of identifying key fields between multiple datasets in vocalize, and matching IDs up based on that, that would be perfect. Does anybody know if this is possible?

Thank you!
icon

Best answer by LaurenK 23 January 2019, 20:48

View original

3 replies

Userlevel 7
Badge +33
yes you can add multipule sources in the vocalize and than you have to map variables with original data set. Any different variable you can add at bottom.
Hi @bansalpeeyush29 thank you for your reply. If I'm reading your response correctly, I am already doing just that. I have added both of the sources to the dashboard and mapped the key ID fields from the 2 sources to the same variable field as seen below:

!

[Instructor ID] comes from the uploaded file and EMP_ID is an embedded data field from the survey, and I mapped them both to the variable named "Instructor ID" for the sake of simplicity.

As I said, though, this is producing a 'union' rather than a 'join'. Looking at the records for 001084901 as an example, for which I populated the survey with test data, the uploaded dataset has 12 records. After mapping the two datasets based on that field, I end up with 13 records: 12 for the uploaded, and one for the survey, evidenced below by how the data is written as N/A for survey fields on uploaded records, and vice versa.

!

My thought is that if it were a true 'join', I'd see the original 12 records from the uploaded dataset only, with copies of the survey response data appended to the left of each of those records, so that I'm seeing all the original records for each ID that is entered into the survey. But instead, it is producing a new record in which none of the original dataset fields are populated.

Is producing that actual 'join' possible? I would love to find out I'm doing something wrong!

Thank you for your help! 🙂
Userlevel 7
Badge +13
Hey @JonathonJames! If you have not yet already, I would recommend reaching out to our Vocalize Support Team, as they will best be able to handle this question!

Leave a Reply