topic Re: How to create bar chart with multiple values in 1 cell? in QlikView

How to create bar chart with multiple values in 1 cell?

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Qlikview Experts,

I had came across a situation whereby I was unable to create the bar chart with multiple values under 1 cell in raw data and I had pasted my raw data below for your reference:

What I hope to achieve is something similar in below, but if there are any other charts can bring the same visual effect would be welcome as well! Thank you for your time in advance!

Best Regards

Re: How to create bar chart with multiple values in 1 cell?

Anil_Babu_Samineni — Thu, 19 Apr 2018 23:54:20 GMT

Does Step1, Step2, Step3 and Step4 are field names that you want to show in X-Axis? Can you share sample in text instead image to improve your question?

Re: How to create bar chart with multiple values in 1 cell?

vishsaggi — Fri, 20 Apr 2018 01:14:41 GMT

For your bar chart to work you should have two dimensions and then count the values of those steps. So without script intervention it cannot be done. I did a Crosstable load of your sample data you posted here and got this. Not sure how your data is going to be in your real world. This is a workaround based on the sample you provided.

Step1: I have copied your data into an excel sheet.

Step2: Used below cross table and loaded this excel sheet with below script.

Sheet2:
CrossTable(Steps, Cnt,1)
LOAD 1 AS Random,
0 AS Step1,
0 AS Step2,
Step3,
Subfield(Step4, ',') AS Step4
FROM
[..\..\QvdEg.xlsx]
(ooxml, embedded labels, table is Sheet2);

Step3: Created a bar chart with below dimensions and expression used.

Add Dim: Steps

Cnt

Expr: = IF(Cnt = 0, Null(), Count(Cnt))

= Cnt&'('&Count( Cnt)&')'

so for these expressions just follow below screenshots modifying few properties highlighted

Here uncheck bar for second expressions and check Value on data points and Text as Popup (optional)

Your chart properties of your bar chart - presentation tab should match with the below highlighted.

Result:

You can change the colors in colors tab accordingly.

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Fri, 20 Apr 2018 01:58:03 GMT

Dear Anil,

Yes. Step 1 - 4 are my field names that I want to show in X-Axis. I had load a sample of my raw data into the Testing.Qvw and shared it to you for your reference.

Best Regards

QianNing

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Fri, 20 Apr 2018 02:00:32 GMT

Dear Vishwarath,

I had only 1 dimension which is my machine number.

Step 1 - 4 are my field names that I want to show in X-Axis. But the numbers in the cell will appeared randomly and ranged from 1 - 99. I had load a sample of my raw data into the Testing.Qvw and shared it to you for your reference.

Best Regards

QianNing

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Fri, 20 Apr 2018 02:40:47 GMT

Dear Anil,

In case you might need the original txt file being loaded into Qlikview. I had also attached a sample of the raw data file for your reference. Thank you so much for your time!

Best Regards

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Fri, 20 Apr 2018 02:41:49 GMT

Dear Vishwarath,

In case you might need the original txt file being loaded into Qlikview. I had also attached a sample of the raw data file for your reference. Thank you so much for your time!

Best Regards

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Fri, 20 Apr 2018 05:20:05 GMT

Dear Vishwarath,

I had tried to use your solution but I can't even load the raw txt file into Qlikview.

Re: How to create bar chart with multiple values in 1 cell?

vishsaggi — Fri, 20 Apr 2018 13:41:54 GMT

No it does not work, coz your field names in your text file are not step1 step2 ...etc. You have @1, @2 ....etc. So just check the attached file and see if this is what you are looking for.

Modified the script with below

CrossTable(Steps, Cnt,1)

CrossTab:

LOAD @1 AS MachineNumber,

IF(Len(Trim(@12)) = 0, 0, Subfield(@12, ',')) AS Step1,

IF(Len(Trim(@13)) = 0, 0, Subfield(@13, ',')) AS Step2,

IF(Len(Trim(@14)) = 0, 0, Subfield(@14, ',')) AS Step3,

IF(Len(Trim(@15)) = 0, 0, Subfield(@15, ',')) AS Step4

FROM

(txt, codepage is 1252, no labels, delimiter is ';', msq);

Re: How to create bar chart with multiple values in 1 cell?

vishsaggi — Fri, 20 Apr 2018 13:43:30 GMT

In future however your values are populated into these 4 step fields, the script i provided should take care of it. Let me know if you have any further issues.

Re: How to create bar chart with multiple values in 1 cell?

s10157754 — Wed, 25 Apr 2018 02:40:35 GMT

Dear Vishwarath,

Thank you for your help! Have a nice day!

Best Regards

QianNing

Re: How to create bar chart with multiple values in 1 cell?

qliksus — Wed, 25 Apr 2018 04:54:12 GMT

One more way of doing it in front end is by using the dual function

Dim : Tyep,Value

Exp: dual( sum(Value) & '(' & Count(Value) & ')', Value)