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

[s10157754]

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

[Anil_Babu_Samineni]

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?

[vishsaggi]

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.

[s10157754]

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

[s10157754]

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

[s10157754]

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

[s10157754]

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

[s10157754]

Dear Vishwarath,

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

[vishsaggi · Accepted solution]

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

FROM

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

[vishsaggi]

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.