Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
AlexWest
Creator
Creator

Count diffirent values in the field

Hi, guys!

I have the following table:

Score Reason
2 Low speed, bad service, rude personal
3 Low speed
1 Bad service, rude personal
5 Great service, fast speed
4 Bad service, low speed

So, as you can see, trough low Scores there are bad Reasons, and through good Scores - good Reasons. Of course, number of Reasons a litle more, then I described, but not so much, I can describe them in Qlik's Script or Chart.

And now, I have to count theese Reasons one by one 🙉

How many "Low speeds", "Bad services", etc. through Scores from 1 till 4?

And how many good Reasons: "Great services", "Fast speeds" etc. through Scores 5?

I understand that I can restrict the chasing of Reasons by Scorse using COUNT(IF(SCORE = 5,REASON),

But I don't know, how to count Reason's value one by one?

Hope someone have any idea to make it. Please help))

Thanks! 

Labels (5)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You'll need to break the Reason field up into multiple reasons using SubField(). You'll also need an identifier for each row to link the "OneReason" back to the Score.  Like this:

Data:
LOAD *, RecNo() as RecId Inline [
Score, Reason
2, "Low speed, bad service, rude personal"
3, "Low speed"
1, "Bad service, rude personal"
5, "Great service, fast speed"
4, "Bad service, low speed"
]
;

Reason:
LOAD RecId, Trim(SubField(Reason, ',')) as OneReason
Resident Data;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You'll need to break the Reason field up into multiple reasons using SubField(). You'll also need an identifier for each row to link the "OneReason" back to the Score.  Like this:

Data:
LOAD *, RecNo() as RecId Inline [
Score, Reason
2, "Low speed, bad service, rude personal"
3, "Low speed"
1, "Bad service, rude personal"
5, "Great service, fast speed"
4, "Bad service, low speed"
]
;

Reason:
LOAD RecId, Trim(SubField(Reason, ',')) as OneReason
Resident Data;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

AlexWest
Creator
Creator
Author

Thanks a lot!

The SUBFIELD function really helped in the question!