Qlik Community

Qlik Sense Cloud Discussions

samuel_lin
Contributor

Multiple Fields into One Single Dimension & Text Processing

Hello Qlik Folks,

*(I recently began to use Qliksense, so everything is build in Qliksense.)

I couldn't find or work out a solution to this so far, but I think there's a way to do it.

I have a survey results asking which are the top 3 companies that people want to work at recorded in three fields, so the data may look like:

Top3[1]     Top3[2]     Top3[3]

Google     facebook     amazon

ebay          amazon        apple

BCG          tesla          google

I want to combine them, for example, I want to get a total count of the all the companies listed. Ideally, I want to combine all these three fields into one single filed but obviously not something like Top3[1] & ", " & Top3[2] & ", " & Top3[3] separated by commas, no.

This was a survey asking over 40 q's, and i have a few results display this way...hopefully some of you may bring me some light of ideas..

Also, is there a way to combine the like names in Qlik sense? for example, case sensitivity, Google & google?

Thanks much guys!

Samuel

1 Solution

Accepted Solutions
samuel_lin
Contributor

Re: Multiple Fields into One Single Dimension & Text Processing

Hi Sinan,

Memory came back and I worked it out (partial code below):

LOAD

[Please list the top 3 companies you would like our Business Development Managers to build relatio...-3] as "Top3",

[ResponseID] as "ResponseID"

FROM [lib://samuel_lin@haas.berkeley.edu/Business_Development_Survey_2.xlsx]

(ooxml, embedded labels, table is Business_Development_Survey_2.c);

Thank you so much for your help, can't do it without you and Andy! Thank you, Sinan!

15 Replies
sinanozdemir
Valued Contributor III

Re: Multiple Fields into One Single Dimension & Text Processing

Hi Samuel,

See the attached and let me know if it works for you:

Capture.PNG

Here is the back-end load script:

Capture.PNG

I am also attaching the qvf as well.

Hope this helps.

Thanks

samuel_lin
Contributor

Re: Multiple Fields into One Single Dimension & Text Processing

Sinan,

Thank you so much!!

It works for getting the total count, but "Top Companies", is disconnected with the rest fields of the source table (say "survey" has 50 more fields such as industry interest, function interest, startup interest....), is there a way to have the rest/original data shake hand with this newly created TopCompanies?

Thanks!

samuel_lin
Contributor

Re: Multiple Fields into One Single Dimension & Text Processing

for the "shaking hand" purpose, I did drop the line: Drop Tables ..., by the way.

and I would appreciate if you could briefly explain to me what does "Upper" and "Resident" do?

Thank you so much!

ogster1974
Honored Contributor II

Re: Multiple Fields into One Single Dimension & Text Processing

‌the above solution would work for you.  I'd probably add rank to thecombined companies table so you could ask additional questions of your data model such as how often was Qlik rated the best company to work for etc...

samuel_lin
Contributor

Re: Multiple Fields into One Single Dimension & Text Processing

Hi Andy,

Could you please give an example of "Rank"? Does adding "rank" occur in loading the data in the backend? I am currently working on and waiting for Sinan's reply to see if I can have the combine companies/top companies shakehand with the rest of the original data set.

With that, there's a unique key "responseID" for each survey, I am trying to load that into the [Combine Companies] as well, but not successful, is it because LOAD can only LOAD one field of the data at once?

samuel_lin
Contributor

Re: Multiple Fields into One Single Dimension & Text Processing

I inserted a few lines, and it seems like the two tables are linked with "ResponseID" from the DataModelViewer, but not linked on the actual developed sheet (if I select one or some fields from the original table, the companies count table become empty...)

below is my actual loading code:

[Combine Top3 Companies]:

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-1") as "Top3",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c;

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-2") as "Top3",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c;

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-3") as "Top3",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c;

ogster1974
Honored Contributor II

Re: Multiple Fields into One Single Dimension & Text Processing

Building on Sinan suggestion

You could build a table that contains the rank information and the link to the question.

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-1") as "Response",

1 AS "ResponseRank",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c;

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-2") as "Response",

2 AS "ResponseRank",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c;

LOAD

Upper("Please list the top 3 companies you would like our Business Development Managers to build relatio...-3") as "Response",

3 AS "ResponseRank",

Upper("ResponseID") as "ResponseID"

Resident Business_Development_Survey_2.c

sinanozdemir
Valued Contributor III

Re: Multiple Fields into One Single Dimension & Text Processing

In terms of Upper(), I just wanted to make all company names upper case so that they follow the same pattern and when you concatenate the tables, company names don't repeat multiple times. And with Resident, you can refer to any other table in your load script before they are dropped.

sinanozdemir
Valued Contributor III

Re: Multiple Fields into One Single Dimension & Text Processing

Would you mind sharing some sample data? Where are ResponseID and company names coming from, Business_Development_Survey_2_c, or another table/sheet?

Community Browser