Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
s10157754
Creator III
Creator III

Count a field with mixed values in a column

Hi Qlikview Experts,

What is the correct syntax in expression to count the mixed values in a field? The number in each cell could be any values ranged from 1 to 99 with a 'comma' in between if there are more than 2 values. I have a set of raw data pasted in below:

Capture.PNG

So I want to create a chart and use expression to display the numbers appeared in '@14' and '@15' columns and the number of times it has appeared. For example, number 50 (11 times), number 51 (2 times) and etc. Any ideas would be greatly appreciated! I had attached my raw data file and qvw for the ease of your reference.

Best Regards

1 Solution

Accepted Solutions
tresesco
MVP
MVP

PFA

Capture.JPG

Update: since you have a personal edition

Script:

CrossTable LOAD @1 as MachineNumber,
SubField(@12,',') as Step2,
SubField(@13,',') as Step3,
SubField(@14,',') as Step4,
SubField(@15,',') as Step5

From <>;

Chart (stacked bar):

Dim1: Type

Dim2:  Value

Exp:

Dual(Value &' : '& Count({<Value={"=len(trim(Value))>0"}>}Value),
Count({<Value={"=len(trim(Value))>0"}>}Value)
)

View solution in original post

16 Replies
tresesco
MVP
MVP

How would your expected output look like here?

s10157754
Creator III
Creator III
Author

Dear Tresesco B,

I hope the output could be display in bar chart or any other charts that can have similar visualization effect. Different number will be represent by a different color.

tresesco
MVP
MVP

Could you draw an outline - what would be the dimension and ...?

vishalarote
Partner - Creator II
Partner - Creator II

I try this manner

is it ok ?

s10157754
Creator III
Creator III
Author

Dear Tresesco B,

I would name @12, @13, @14 and @15 as Step1, Step2, Step3 & Step4 respectively. So I would like to have my dimension to be Step1, Step2, Step3 and Step4. Then inside the bar chart it will show the different type of numbers with the number of times beside it. Something look similar like this

untitled.png

Best Regards

s10157754
Creator III
Creator III
Author

Dear Vishal,

Due to personal edition, I was unable to open the qvw file sent by you. Could you show me a screenshot on the outcome? Thanks for your help in advance!

Best Regards

cameliap
Partner - Contributor
Partner - Contributor

Could you maybe create a new table with all the separate values in one field? Like this:

ValueCount:
LOAD SubField(@13,',') as Value
Resident SourceData;

Concatenate(test_count) LOAD
SubField(@14,',') as Value
Resident SourceData;

Concatenate(test_count) LOAD
SubField(@15,',') as Value
Resident SourceData;

Then you could just create a chart with "Value" as dimension and count(Value) as measure.

tresesco
MVP
MVP

PFA

Capture.JPG

Update: since you have a personal edition

Script:

CrossTable LOAD @1 as MachineNumber,
SubField(@12,',') as Step2,
SubField(@13,',') as Step3,
SubField(@14,',') as Step4,
SubField(@15,',') as Step5

From <>;

Chart (stacked bar):

Dim1: Type

Dim2:  Value

Exp:

Dual(Value &' : '& Count({<Value={"=len(trim(Value))>0"}>}Value),
Count({<Value={"=len(trim(Value))>0"}>}Value)
)

vishalarote
Partner - Creator II
Partner - Creator II

Capture3.PNG