Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

Hello All,

As i am new to QlikSense, i have a task to produce the graph and which takes data from the db.

DB Tables:

Survey

survey_id    lender_offer_id              answer_text 

10                       1                           Strongly Agree

11                       1                           Agree

12                       2                           Excellent

13                       2                           Above Average

14                       3                           Strongly Agree

15                       3                           Agree

Here is my Script:

LOAD `survey_id`, 

    `lender_offer_id`,

    `survey_question_id`,

    `answer_text`;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

My Goal is to produce a bargraph that must produce a percentage to know how much percent of people opted to particular Option in the Survey.

But restriction here is i must take Strongly Agree and Excellent as Strongly Agree, Agree and Above Average as Agree by totalling.

For eg:

In the above table total 3 persons opted for Strongly Agree(Included Excellent) and 3 for Agree(included Above Average).



How can i do that using  QlikSense.

1 Solution

Accepted Solutions
Partner
Partner

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

I would change your second one as follow:

=if((Match(answer_text,'Strongly Agree','Excellent')),'Strongly Agree',if(Match(answer_text,'Agree','Above Average'),'Agree'))

Capture.PNG

16 Replies
Partner
Partner

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

First thing, I would create a new field in the script :

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Strongly Agree' as Answer,

    `answer_text`

where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Agree' as Answer,

    `answer_text`

where `answer_text` ='Agree' or `answer_text`='Above Average'

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

Now in your bar chart:

As dimension; choose your newly created field: Answer

As a measure:

count(lender_offer_id  ) / count({1}total lender_offer_id )

and format it as %.

Not applicable

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

Thanks for ur reply,

But here i need to get the counts for only two questions out of many, ie users who opted those options in two questions.

And i need to change this in expressions,because i can't able to make any changes in script,it'll affect further charts.

Best Regards

Partner
Partner

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

That won't affect a thing, you could always use your exsting fields in other charts, and use only your new field in the bar chart you're willing to make :

If you have other answers, you can add Everything, like THIS. and do NOT CHANGE A THING in your other charts, they won't be affected.

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Strongly Agree' as Answer,

    `answer_text`

where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Agree' as Answer,

    `answer_text`

where `answer_text` ='Agree' or `answer_text`='Above Average'

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Other as Answer,

    `answer_text`

where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average'

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY;

Now in your bar chart, If I fully understand, you  want to divide by the count of these 2 answers (strongly agree, and agree?)

Do as follow:

As dimension; choose your newly created field: Answer

As a measure:

count(lender_offer_id ) / count({<Answer={'Agree','Strongly Agree'}>}total lender_offer_id )

and format it as %.

Not applicable

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Other as Answer,

    `answer_text`

where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average'


Why you are adding this,,can u pls explain it

Partner
Partner

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

I thought you had answers other than

Strongly Agree' ,'Excellent' ,'Agree' and 'Above Average' don't you?


Not applicable

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

And also i'm getting error saying:

The following error occurred:

Unknown statement: where

---

The error occurred here:

>>>>>>where<<<<<< `answer_text` ='Strongly Agree' or `answer_text`='Excellent'

Not applicable

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

Yea it's in other questions,,i mean to say after question 2.

Partner
Partner

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

Try as follow:

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Strongly Agree' as Answer,

    `answer_text`

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY

where `answer_text` ='Strongly Agree' or `answer_text`='Excellent'


;

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Agree' as Answer,

    `answer_text`

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY

where `answer_text` ='Agree' or `answer_text`='Above Average'


;

LOAD `survey_id`,

    `lender_offer_id`,

    `survey_question_id`,

     'Other as Answer,

    `answer_text`

;

    SQL SELECT `survey_id`,

    `user_id`,

    `lender_offer_id`,

    `batch_meta_data_id`,

    `survey_question_id`,

    `answer_text`

FROM SURVEY

where `answer_text` <>'Strongly Agree' and `answer_text`<>'Excellent' and `answer_text` <>'Agree' and`answer_text`<>'Above Average';

Not applicable

Re: Bar Graphs to take input from multiple fields(columns in db) combining those to produce single value

Some Sync error:

2:48:53 TT

Output cleared.

2:48:56 TT

App successfully saved.

---

Started loading data

---

Connecting to Prod Db (win-f87f4njanpb_administrator)

Connected

SURVEY

Lines fetched: 26,514

SURVEY << SURVEY

Lines fetched: 38,572

SURVEY

Lines fetched: 15,662

$Syn 1 = survey_id+lender_offer_id+survey_question_id+answer_text

---

App saved

---

Finished with error(s) and/or warning(s)

0 forced error(s)

1 synthetic key(s)