How to counting comments in a Colum

[mandilicious]

Hi Champs 

Still new, i would like to implement below scenario from excel to qliksense.

First i need to count customer comments and get the number of comments, then sum number of comments to get Total number of comments. Then after get the percentage by doing like this Number of Comments(1) / Total number of comments(28) to get % = 3.57.

Regards

[Adam_Romanowski]

Maybe

Count(Number of Comments]/Count(TOTAL Number of Comments]) and set format  of column to %

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/define-...

[mandilicious]

Hi Adam,

Thank you for respond.

If i may ask what formula can i use to count customer comments occurrences?

 What script can i use to count comments For example count  how many 'Stock feed issues' comments do we have?

Thanks

[Adam_Romanowski]

It depends from data model you have. I guess, you have Excel data with columns such as Comment, Date, Id etc. you can load it into Qlik, add a table and check results.

The requirement is to add Comment as dimension, and formulas with count functions as below.

Here is my example script:

let vToday=Today();
load * inline [
Id, Customer Comment, Date
1, Stock feed issue, $(vToday)
2, Stock feed issue, $(vToday)
4, Stock feed issue, $(vToday)
5, Latency, $(vToday)
6, Latency, $(vToday)
7, Latency, $(vToday)
8, Latency, $(vToday)
9, Latency, $(vToday)
];

 and here is my table, I haven't touched the labels so you can see formulas

[mandilicious]

Hi Adam,

Thanks for the above.

Is the other method i can use without using variable(vToday)? if not is fine.

I would like to know because comments sometimes are not only one word e.g. it will be like ' the delay between an instruction to transfer'  what script can i use to count this comments as latency?

Thank you in advance.

Thanks

[HeshamKhja1]

Hi @mandilicious ,

So as you probably know, Qlik Sense has the backend script and the frontend expression. The backend script is what creates the data model. The frontend expression make use of the data model to show numbers.

What @Adam_Romanowski wrote as a script in his comment is just an example of dummy data. It is an inline load to create a table in the data model.

Now in your case, you have to load the data from your source. If it is excel then load it into Qlik Sense and you will be able to view it in the data model. I expect it to be similar to the structure of Adam's table.

After you load the data, you can use the frontend expression which is

Count([Customer Comment]/Count(TOTAL [Customer Comment])

 to get the final number.

Hope it helps and let me know if you need further details.

[mandilicious]

Hi Heshamkhja1,

Thank you for clarifying that. I really appreciate.

My concern is since from comments Colum we dont only have one word like 'Latency' it comes in a way of long sentences ,like this comment  ' the delay between an instruction to transfer'/ 'The test measures sleep latency/

App platforms to err on the side of waiting  which all means Latency rite?.

I wanted to check if there is any script i can use in such sentences to count such as latency?

Apologies i am asking obvious or answered question?

Regards

[HeshamKhja1]

Ahh I see. Did you check the WildMatch() function in Qlik Sense?

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalF...

You can use it to with combination of the Pick() function to create a new column that has the keywords of the original comments field.

For example:

Pick(WildMatch(CommentField, '*latency*'), 'Latency') AS CommentFieldKeyWord

Does this answer the question? or am I still far?

[HeshamKhja1]

@mandilicious , did you get your answer? or you still need further help?

[mandilicious]

Hi Heshm

Sorry for late respond, please see below script. I am doing this correctly?

Ratings_Details:
LOAD
"Date Delivery",
"Overall Rating",
"Overall Comment",
Pick(WildMatch( "Overall Comment", '*latency*'), 'Latency') AS Latency,
Pick(WildMatch( "Overall Comment", '*Incomplete*'), 'Incomplete') AS Incomplete,
Pick(WildMatch( "Overall Comment", '*Not Delivered*'), 'Not Received') AS Not_Received,
Pick(WildMatch( "Overall Comment", '*No Communication*'), 'Lack of communication') AS communication,
Pick(WildMatch( "Overall Comment", '*Price*'), 'Total Price') AS Price,
"Delivery Status"
FROM [Qlik Sense - Ratings Details.xlsx]
(ooxml, embedded labels, table is Sheet1);