Add dimensions to straight table using "field index"

[pedrohenriqueperna]

Hi,

Recently I've brought google forms response tables to Qlik and I'm trying to figure out the best way to model and visualize the data. The issue is that different surveys have a different number of questions and headers. I'm not interested in having a table for each survey, my idea is to concatenate them in a single table, or maybe 2 tables, 1 being a fact and another a dimension table holding the questions.

Idea 1:

If I simply concatenate the tables I would have a big number of columns with different and unpredictable names. I was hoping I could reference to them in a straight table using an index. For instance, adding a new dimension using something like FieldIndex(1), FieldIndex(2), etc, and somehow that would return the column name (I know fieldindex doesn't do that, it's just an example).

Idea 2:

My other idea was to add an index number for each column/question/header in the script/loader. While the first data row (considering the index is now the header) would have the question itself. That way I could make 'n' columns in a straight chart table, and dynamically display only those that meet some criteria.

The raw table/survey would look like this:

And it'd look like this after renaming the fields to an index:

In this case I could have a flag to identify the questions in the first row, or simply consider that the first row [0] is equal to the header. The problem is that now I have to use Dual() and/or something else to display the actual question, instead of the index. I'm not sure if this approach is good at all.

I was actually hoping someone could give me ideas or confirm that I'm on the right path. Maybe I'm just complicating things.

[Kushal_Chawda]

@pedrohenriqueperna  You need to use crosstable functionality here. Please take a look at the link below

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

[pedrohenriqueperna]

Hi, Kushal, thanks for replying

I actually thought about and tried using the crosstable func, but only to build a mapping table for the headers/questions.

I don't understand how transposing the questions would do the job. Could you give me more details?

Mainly, my struggle is to dynamically display concatenated survey response tables that has nothing to do between them. Considering each question is a column header, I can't dynamically show the questions in chart objects without having to hard code the column/question/dimension name. My goal is to add a variable input dropdown to select different surveys and then display the questions for the selected one. The only way I could imagine it to work would be if I had index numbers for the headers, instead of the questions.

Moreover, each row represents an answer and the answers are way bigger in volume/quantity than the number of columns. If I crosstable, I would end up with the answers as columns/headers and the questions as rows, is that right?