Hi @D19PAL ,
The Generic load creates one table per category in your data. In your case the tables will consist of a form_id and one question per table. The rows will be the answers. Once you've used the generic table they can be merged back together again based on your form_id. One the tables are back together then you can use an if statement. Below is some code you can add to perform this.
Result:
load Distinct
form_id
From [lib://AttachedFiles/Book2.xlsx]
(ooxml, embedded labels, table is interest);
//Unpivot so that the table headings are the questions
gen:
Generic Load
form_id,
quest_id,
"text"
From [lib://AttachedFiles/Book2.xlsx]
(ooxml, embedded labels, table is interest);
//Generic Load creates a dataset for each attribute of the pivoted item
// Loops through all the tables created in the generic load above and joins them all
//to the "Oneline" table that has one line per form_id and column with questions
FOR i = NoOfTables()-1 to 0 STEP -1;
LET vTable=TableName($(i));
IF WildMatch('$(vTable)','gen.*') THEN
LEFT JOIN (Result) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i
let vTable =;
Thanks
Anthony
