I have a survey database where each line is a survey answer and each column is a question.
The survey answers will always be a number instead of text, for example, for a specific question:
1 = very unsatisfied
2= unsatisfied
3=satisfied
4=very satisfied
for another question:
1= yes
2= no
and so on for a very big amount of questions.
I created a mapping table (see screenshot below) with the first column is the question name and it is the same as my column names in my database. Second column is the answer number and 3rd column is the text I would like to replace the 2nd column with.
I would like to find a way to replace the numbers by the text value in my mapping table 3rd column
You May give the joiner node a try.
Create separate tables for the value pairs and join the column value in the answer to the ID im the Valentinstag pair table.
I using the examples you provided in your first post.
Read the data from your survey DB into a first table (t_survey) with the code in the column Arena Entrance Satisfaction
Use the Table Creator Node Table Creator — NodePit to statically create the auxiliary table (t_satisfaction) with the code (1…5) and it’s text descriptions.
Now apply the Joiner Node Joiner — NodePit and join the column Arena Entrance Satisfaction from the first table to the column Answer Category in the second table.
That will create a new table (based on t_survey) with an additional column containing the text.
The approach mentioned by @AlexanderFillbrunn may be another solution to your challenge. As of now I did not try this approach. So I’m sorry that I can’t give you more details for that at the moment.
You see that there is not only one solution for data wrangling challenges.
I was able to do what I wanted using your node, however I need to do that on more than 100 columns, so I don’t want to duplicate my node 100 times. So I tried using a column list loop start and it looks like this:
In the value lookup node, I put in the first column I want to do the lookup with, however when I run the loop, it gives me the following error message:
"Input table’s structure differs from reference (first iteration) table: different column count 49 vs 50
Do you know what it means and how I can fix it?
And other times it would also give me the error message at my value lookup: “No such column as (Column Name)”. It is like the loop changes the first column to do the iteration on and switches it for the 2nd column in my column list loop
Thanks for your solution however this would not work as I have more than 100 questions and for a specific question 1 might mean “Yes” but for another question 1 might mean “hello”.
So with a join, I would only get yes for all 1s
However if you can help me with my loop above that would be of great help
Hi @etsimard , if you were able to upload some sample data it would be easier for people to provide you with actual workflow examples rather than abstract ideas.
With the column list loop, for example, you will find that on each iteration, it provides one of the columns that you are iterating plus all of the other (non-iterated) columns. Typically you would need to put in a column filter so that only the specific column of interest is returned by the loop and then you would finish the loop with a “loop end column append” node and finally join back the resultant table to the original columns that you didn’t iterate over.
If you can upload a sample of the workflow, or data, that you have so, it would be easier to demonstrate what I mean.
Hi @etsimard , yes the workflow uploaded fine thank you.
I haven’t much time today to give explanations, but take a look at this flow and see if it does what you need.
In brief… on each iteration, it uses only the rows in the lookup table containing the current column name in the “ID Response” column. Within the loop, it renames the “Answer” column generated by the Value Lookup as the current column name. At the end of the loop it collects on these column names (using Loop End Column Append) and finally reassembles the table.
Sorry for my late reply to your comment.
Your objection is correct if you use only one join dictionary.
What I understood is that your are having multiple of them (e.g. for question 1, another for question 2). So you will join the corresponding table to each column.
I understand that you are on a good track with the solution of @takbb. So maybe it’s better to focus the energy to this approach.
