It is possible to map 1 dimension across multiple columns?

[QlikUser2025]

Hello,

I would like to evaluate the data from a user survey, and the results are available as a CSV file.
For some questions in the survey, it was possible to give multiple answers per question.
Please take a look at the attached screenshot. Columns B and C show the answers per user.
Columns D and E can be evaluated normally in Qlikview.

But how do I tell Qlikview that
a) the different column names (row A) in B and C are the same dimension and
b) the results of both columns (B and C) basically belong together?

Regards

[Daniel_Castella · Accepted solution]

Hi @QlikUser2025 

I'm not sure which is the outcome that you are expecting.

If you want to combine both answers in a single row, try this code:

A:
LOAD
Id,
"Question 1 Color Green"&' '&"Question 1 Color Blue" as "Question 1 Color",
"How old are you?",
"Your Income?"
FROM [lib://DataFiles/Input.xlsx]
(ooxml, embedded labels, table is Sheet1);

If you want to add both answers in independent rows but in the same dimension, try this one:

A:
LOAD
Id,
"Question 1 Color Green" as "Question 1 Color",
"How old are you?",
"Your Income?"
FROM [lib://DataFiles/Input.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate (A)
LOAD
Id,
"Question 1 Color Blue" as "Question 1 Color",
"How old are you?",
"Your Income?"
FROM [lib://DataFiles/Input.xlsx]
(ooxml, embedded labels, table is Sheet1);

If none of these solutions work for you, let me know which is the output table you are expecting.

Kind Regards

Daniel

[QlikUser2025]

Hello,

Thanks for your answer. At this moment my experience with Qlik Cloud Sense

is rudimentary. Where do I have to enter your code lines?

> If none of these solutions work for you, let me know which is the output table you are expecting.

I added another screenshot. I hope it's clearly what I want to try to realize. If i select 

the age "1", the dashboard should count the answers "green" and "blue". Result

2 blue, 1 green. 

Regards

[Daniel_Castella]

Hi @QlikUser2025 

I understood by your first comment that you were using Qlikview, not Qlik Cloud. Are you using Data Manager or Data Load Editor to load the data? The code I sent is to be set in Data Load Editor.

I mean, Data Manager is better to be used when your inputs are already transformed since the transformations available there are limited. It is more user friendly, but if you need to perform transformations on your input (like this case), it is better to work with Data Load Editor.

Then, according with the second image you sent, you need the second code I wrote. However, notice that you will have duplicates in "How old are you?" and "Your Income?" fields. To avoid these duplicates to impact on your chart you will need to set the measure as Count(Distinct Id), like in the picture below.

Kind Regards

Daniel

[QlikUser2025]

Hello,

>  Are you using Data Manager or Data Load Editor to load the data? 

I have used the Data Manager and I tried first to find a solution inside the Data Manager to migrate both columns.

Now I tried the Data Load Editor. I used your 2nd. code above to load the data and it looks better.

And you are right, the 2nd code duplicates the other columns. 

Take a look at the screenshot.

answ_3_code_pic_2.jpg

If you say, i have : "to set the measure as Count(Distinct Id),"

you mean, it is only possible in the visualization mode and not inside the data load Editor, correct?

I tested it with your code, and the result is the following. 

See : answ_3_result_pic_3.jpg

Exists there a documentation with code line examples for the Data Load Editor?

Regards

[Daniel_Castella]

Hi @QlikUser2025 

In response to your questions.

you mean, it is only possible in the visualization mode and not inside the data load Editor, correct?

It depends on how you build the backend. I mean, there is not a unique way to obtain the same output. I try to follow Qlik Best Practices for the app to be as most efficient as possible and with a clean and easy script and formulas.

Another option that comes to my mind could be to set a counter field in the backend. Then, you could make it to be 1 for each line except for the duplicated ones that would be 0 and make a sum of this counter in the chart. In this way, the distinct would not be needed. However, to write this in the script is a bit more complicated than simply using a distinct in the chart.

Other probably option is to use only formulas in the visualizations without applying any transformation in back end. However, I would not recommend it since then the formulas become long and difficult and it is worse for the performance. For little apps there is no problem, but it is better to move all the complex calculations to backend.

In summary, you need to know which design you want to have and then build the backend and the frontend aligned together with this design. In this case, I built the backend knowing that I’m duplicating the "How old are you?" values and, for this reason, I align the frontend using a distinct in the formula in its chart.

Exists there a documentation with code line examples for the Data Load Editor?

Yes, you can find a lot of trainings on Qlik official channels. For example:

Using the data load editor | Qlik Sense on Windows Help

Tutorial - Scripting for beginners | Qlik Cloud Help

Qlik Cloud Code Examples | Qlik Developer Portal

Home : Qlik

Kind Regards

Daniel