Hi Rob,
I really appreciate the feedback here!! I provided some example code of what I am doing below which I have simplified for this purpose. For reference I am creating one consolidated table from the resulting tables in the Generic Load, and now want to perform a CrossTab on that resulting table (Seems possible although could use guidance). The reason for doing this may be hard to explain, but simply stated the original format of this survey is very sloppy, and there is data we need to ignore for our actual analysis. Joining the Generic Load tables helps, but still leaves difficulty in creating formulas on that data because it is one gigantic matrix. Performing a CrossTab to create just two columns would make analysis a lot easier in my opinion. (Also, I am a Qlik newbie so I may be missing something here)
//Initial Data Load
[Survey_Answers]:
LOAD surveyid,
suvey_sectionid,
survey_sectionname,
survey_category
survey_questionid,
survey_questionname,
survey_answervalue,
date;
SQL SELECT *,FROM <ExternalDataSource>;
//Generic Load (Creates a table for each Survey Question with the answers as a value in that field
[Generic_Load_Survey_Answers]:
Generic LOAD
surveyid,
survey_questionname,
survey_answervalue
Resident [Survey_Answers];
//This code joins all of those disparate tables into one consolidated table which makes most calculations easier
[Generic_Survey_Answers_Join]:
LOAD Distinct surveyid Resident [Survey_Answers];For i=NoOfTables()-1 to 0 STEP -1;
Let vTable = TableName($(i));
IF WildMatch('$(vTable)','Generic_Load_Survey_Answers.*') THEN
LEFT JOIN ([Generic_Survey_Answers_Join]) LOAD * RESIDENT [$(vTable)];
Drop Table [$(vTable)];
ENDIF
Next i
//This is the code that is not working for me. I would like to now unpivot specific columns as there are many fields (survey_questionname) in the above table we don't need. I hope to produce two columns from this CrossTable Load. One that only Includes questions we care about and answers we care about. These 10 questions below are not needed, and I want to remove from my "analysis" columns
[Survey_CrossTable_Answers]:
CrossTable(Survey_Questions_UnPivot,Survey_Answers_UnPivot,10)LOAD
surveyid,
question1 as question1_CrossTab,
question2 as question2_CrossTab,
question3 as question3_CrossTab,
question4 as question4_CrossTab,
question5 as question5_CrossTab,
question6 as question6_CrossTab,
question7 as question7_CrossTab,
question8 as question8_CrossTab,
question9 as question9_CrossTab,
question10 as question10_CrossTab,
Resident [Generic_Survey_Answers_Join];
*The error I get is "Generic Fields are not found", but I have cross-referenced my[Generic_Survey_Answers_Join] table and I see them in there
