Count a field with mixed values in a column

[s10157754]

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

[s10157754]

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

[tresesco]

Read crosstable.htm , they are default arguments.

[s10157754]

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

[tresesco]

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.

[s10157754]

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

[tresesco]

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

[s10157754]

Dear Tresesco B,

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

Best Regards

QianNing