topic Re: Count a field with mixed values in a column in QlikView

Count a field with mixed values in a column

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

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:

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

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 06:44:32 GMT

How would your expected output look like here?

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 07:10:02 GMT

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.

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 07:20:18 GMT

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

Re: Count a field with mixed values in a column

vishalarote — Fri, 20 Apr 2018 07:34:13 GMT

I try this manner

is it ok ?

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 07:45:46 GMT

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

Best Regards

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 07:47:49 GMT

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

Re: Count a field with mixed values in a column

cameliap — Fri, 20 Apr 2018 07:51:10 GMT

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.

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 09:31:50 GMT

PFA

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

Re: Count a field with mixed values in a column

vishalarote — Fri, 20 Apr 2018 09:51:40 GMT

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 11:16:31 GMT

Dear Tresesco B,

You solution works like a charm! Thank you so much for helping me! But may I know how did you get dimension 'Type' and 'Value'? I can't figure it out

Best Regards,

QianNing

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 11:31:40 GMT

Read crosstable.htm , they are default arguments.

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 11:36:02 GMT

Dear Tresesco B,

I had added your loading script into my main dashboard loading script. But I realized after I add in your script, my other fields will not be display in a neatly manner, this will affect my other charts in the main dashboard.

Is there a way for me to add in your script without affect my main dashboard? Thanks for your help in advance!

Best Regards

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 11:44:03 GMT

Crosstable has answer to your problem. Try like:

Crosstable (Type, Value, n)

LOAD

@1 as MachineNumber,

...

@20 as Step4MIT

SubField(....)

...

Note: 'n' is the number of fields that you want to keep them intact (remember those fields should be at the beginning of the load statement, because qlik would consider first n number of fields from the load statement). For better understanding read more about the function from the help link provided above.

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 11:54:03 GMT

Dear Tresesco B,

I tried but it doesn't work. Appreciate if you could help me to build into my main dashboard, otherwise I can't create other charts based on other fields of data.

Best Regards,

QianNing

Re: Count a field with mixed values in a column

tresB — Fri, 20 Apr 2018 12:54:30 GMT

'n' should be a number. For your above script, try replacing n with 3.

Re: Count a field with mixed values in a column

s10157754 — Fri, 20 Apr 2018 13:08:15 GMT

Dear Tresesco B,

Thanks for your correction! It works now! Have a nice day!

Best Regards

QianNing