Hi there,
I have a client who has been collecting data from surveys from her customers. There are two different types of customer service surveys her customers can fill out. The first survey allows her customers to answer questions about the design and navigability of her department's website (I'll call the table for this data [WEBSurvey]). The second survey allows her customers to answer questions about the service they received (I'll call the table for this data [SERVICESurvey]).
The data from each table looks like this:
LIB CONNECT TO 'My connection to the first data source';
RestConnectorMasterTable:
SQL SELECT
"survey_date",
"overall_how_satisfied_are",
"comments"
FROM JSON (wrap on) "root";
[SERVICESurvey]:
LOAD
RowNo() as [Survey ID],
Date(Date#(Left([survey_date], 10), 'YYYY-MM-DD'), 'M/D/YY') as [Survey Date],
"overall_how_satisfied_are" as [Overall, how satisfied are you with the customer service provided?],
"comments" as [Comments],
RESIDENT RestConnectorMasterTable;
DROP TABLE RestConnectorMasterTable;
LIB CONNECT TO 'My connection to the second data source';
[WEBSurvey]:
LOAD ResponseId,
LineNo,
Question,
Answer,
QuestionFamily,
QuestionSubtype;
SELECT ResponseId,
"LineNo",
Question,
Answer,
QuestionFamily,
QuestionSubtype
FROM "surveyconnect".SurveyMonkey.WEBSurvey;
The field [Overall, how satisfied are you with the customer service provided?] in the [SERVICESurvey] dataset can have the following values: Very Satisfied, Somewhat Satisfied, Neutral, Somewhat Dissatisfied, and Very Dissatisfied.
One of the values the field Question from the [WEBSurvey] dataset can have the is Overall, I am satisfied with my experience. The field Answer from the [WEBSurvey] dataset can have the following values: Strongly Agree, Agree, Neutral, Disagree, and Strongly Disagree.
My client has asked me to create a new field, AllSatisfactionRatings, assign a value based on customers' response to each survey: Very Satisfied, Somewhat Satisfied, Somewhat Dissatisfied, and Very Dissatisfied. Customers who have the value Neutral in the Answer field would be assigned Somewhat Dissatisfied.
I'm a little stumped as to how to go about this. At first I thought I could make the AllSatisfactionRatings field using nested if statements for each field within the two tables--one of them would look something like this:
if([Overall, how satisfied are you with the customer service provided?] = 'Very Satisfied','Very Satisfied',
if([Overall, how satisfied are you with the customer service provided?] = 'Satisfied','Somewhat Satisfied',
...and so on, but then I wondered if there was a way I would be able to mesh the two tables together.
What do you think would be my best option, and how would I go about it? Any recommendations or advice are welcome, and please let me know if you need me to clarify or add more details here. Thanks in advance!
