topic Re: Using Fields from Two Different Tables in One Table in App Development

Using Fields from Two Different Tables in One Table

MTS95 — Thu, 16 Feb 2023 15:09:33 GMT

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!

Re: Using Fields from Two Different Tables in One Table

Sabrina_V — Thu, 16 Feb 2023 10:23:02 GMT

The new field in common for the two tables ? I don't sure I understand well your problem but I think you can concatenanate your two tables. And , in the result table, create your new fields. In order to distinguish the two tables, maybe create a field "Data Source" with the name of the type of survey.

Re: Using Fields from Two Different Tables in One Table

MTS95 — Tue, 21 Feb 2023 15:50:55 GMT

I appreciate your feedback! I apologize; I did not explain my problem very clearly.

In case someone encounters a similar problem in the future, I'll record what I ended up doing to the app:

My client has two tables with the results of two different surveys in her Qlik app. The first survey, [WEBSurvey], has the field Answer for recording the level of satisfaction of her customers for two different values in the Question field, like so:

Question	Answer
How would you rate the quality of this website?	Excellent
How would you rate the quality of this website?	Good
How would you rate the quality of this website?	Satisfactory
How would you rate the quality of this website?	Unsatisfactory
How would you rate the quality of this website?	Poor
Overall, I am satisfied with my experience	Strongly Agree
Overall, I am satisfied with my experience	Agree
Overall, I am satisfied with my experience	Neutral
Overall, I am satisfied with my experience	Disagree
Overall, I am satisfied with my experience	Strongly Agree

The second survey, [SERVICESurvey], has the field [Overall, how satisfied are you with the customer service provided?], which has the values:

[Overall, how satisfied are you with the customer service provided?]

Very Satisfied

Somewhat Satisfied

Neutral

Somewhat Dissatisfied

Very Dissatisfied

My client asked for a single field, called something like AllSatisfactionRatings, that assigns a value to each possible response for both tables. Optimally, it would look something like this:

Answer	[Overall, how satisfied are you with the customer service provided?]	AllSatisfactionRatings
	Very Satisfied	Very Satisfied
	Somewhat Satisfied	Somewhat Satisfied
	Neutral	Somewhat Dissatisfied
	Somewhat Dissatisfied	Somewhat Dissatisfied
	Very Dissatisfied	Very Dissatisfied
Excellent		Very Satisfied
Good		Somewhat Satisfied
Satisfactory		Somewhat Dissatisfied
Unsatisfactory		Somewhat Dissatisfied
Poor		Very Dissatisfied
Strongly Agree		Very Satisfied
Agree		Somewhat Satisfied
Neutral		Somewhat Dissatisfied
Disagree		Somewhat Dissatisfied
Strongly Disagree		Very Dissatisfied

I wasn't able to create this table without making some cyclic groups (there were other tables in the data load that complicated this), so, rather than create one table with one field to cover all possible answers, I made two tables with two separate fields, like so:

Answer	WEBSatisfactionRatings
Excellent	Very Satisfied
Good	Somewhat Satisfied
Satisfactory	Somewhat Dissatisfied
Unsatisfactory	Somewhat Dissatisfied
Poor	Very Dissatisfied
Strongly Agree	Very Satisfied
Agree	Somewhat Satisfied
Neutral	Somewhat Dissatisfied
Disagree	Somewhat Dissatisfied
Strongly Agree	Very Dissatisfied

[Overall, how satisfied are you with the customer service provided?]	SERVICESatisfactionRatings
Very Satisfied	Very Satisfied
Somewhat Satisfied	Somewhat Satisfied
Neutral	Somewhat Dissatisfied
Somewhat Dissatisfied	Somewhat Dissatisfied
Very Dissatisfied	Very Dissatisfied

There might still be a way for me to use these two new tables to make one table with the single field AllSatisfactionRatings, so if anyone has any advice on that, feel free to let me know. Thanks again!