Convert JSon value to column header

[SonOfJeffGoldblum]

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

[dplr-rn]

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.

[SonOfJeffGoldblum]

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()

[dplr-rn]

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

[SonOfJeffGoldblum]

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

[dplr-rn]

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

[dplr-rn]

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

[SonOfJeffGoldblum]

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.

[SonOfJeffGoldblum]

Ah, let me take a  look at that as that may be why I'm getting many duplicates to each question.

[SonOfJeffGoldblum]

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?