Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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

Highlighted
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)