Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a SQL Server loading a column, AnswersJson and would like to convert the values within Question to be a column header with the AnswersJson portion "AnswerText" to associate with that column.
Id | Question | QuestionNumber | AnswersJson | CreatedDate |
1 | How was your service | 5 | [{"AnswerPart":null,"AnswerColumn":null,"AnswerText":"Great","AnswerOther":null,"AnswerExportValue":"","AnswerComment":null}] | 7/29/2019 |
2 | How could we have done better | 6 | [{"AnswerPart":"","AnswerColumn":"","AnswerText":"service time","AnswerOther":null,"AnswerExportValue":"","AnswerComment":null},{"}] | 7/29/2019 |
3 | Would you recommend us | 7 | [{"AnswerPart":null,"AnswerColumn":null,"AnswerText":"yes","AnswerOther":null,"AnswerExportValue":"","AnswerComment":null}] | 7/29/2019 |
4 | Do you have any questions | 8 | [{"AnswerPart":null,"AnswerColumn":null,"AnswerText":"No","AnswerOther":null,"AnswerExportValue":"","AnswerComment":null}] | 7/29/2019 |
|
Expected Result:
Id | How was your service | QuestionNumber | CreatedDate |
1 | Great | 5 |
Thank you
Question is answer json have nested values?
if not you can use a combination of string functions and a pivot table to achieve it.
e.g. PurgeChar(
SubField( SubField('[{"AnswerPart":null,"AnswerColumn":null,"AnswerText":"Great","AnswerOther":null,"AnswerExportValue":"","AnswerComment":null}]',',',3)
,':',2)
, chr(34))
will give you the answer value.
I have done this before ubt when I want to add all of the quesionts as headers, it results in a separate row for each answer text.
Id | How was the service | How was the experience | Would you Recommend | Do you have any questions | CreatedDate | |
1 | Great | today() | ||||
1 | Good | today() | ||||
1 | Yes | today() | ||||
1 | No | today() | ||||
I'd like it to be:
1 | Great | Good | Yes | No | today() |
Ok. You might need to do generic load. Which is kind of opposite of crosstable load
check below
https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470
I am not familiar with a generic load and reading through the link is a tad confusing. Would I go about this like:
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericLabel' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
AnswerDetails:
Load distinct Question From AnswerDetails;
For each vTableName in $(vListOfTables)
Left Join (AnswerDetails) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName
You do generic load like so
Generic Load Key, Attribute, Value From … ;
that results in multiple tables . but you will get proper results in UI.. And according to hic in the link shouldnt have any performance impact.
But If you want to combine it all into a single table again thats when you use the for loop
missed to add. when i have used generic load. i have used below code to combine it back (from rwunderlich's comment in that url).
Only reason to combine it back is because it looks cleaner. (HIC recommends not to 🙂 )
CombinedGenericTable:
Load distinct Key From GenericDB;
FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'GenericLabel.*') THEN
LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
Performed Generic Load:
Generic Load Id, Question, AnswerText, CreatedDate FROM [Table]
1 | How was our service | Great | |
1 | How was our service | ||
1 | How was our service | ||
1 | How was our service | ||
2 | How was our service | ||
2 | How was our service | Excellent | |
2 | How was our service | ||
2 | How was our service |
For every Id, it creates rows for as many Id's there are. In other words, if I have 10 surveys I get 10 rows of responses where one row shows the true response for that person.
Ah, let me take a look at that as that may be why I'm getting many duplicates to each question.
In this snippet, am I adding in my actual table names or using the literal 'combinedgenerictable' and 'tablename'? Do I perform a SELECT FROM as well after this load?