Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
This is my first post and my first time using Qlik Sense so hopefully I've come to the right place!
I'm working with social media data, and within the data set, I have people who've mentioned my brand from 1 - 500 times. I'd like to group these as different sets:
Group A: mentioned brand 100 - 500 times
Group B: mentioned brand 25 - 99 times
Group C: mentioned brand 12 - 24 times
Group 😧 mentioned brand 6 - 11 times
The data simply lists all the mentions so the 'people' column in the data set will have duplicate records.
Not only do I want to group these people, I'd like to know how many people are in each group as a KPI and how many total mentions each group has.
Any help would be appreciated.
try the following approach (you must edit FROM stetement with your source definition):
MyTable:
LOAD
"Query Id",
"Query Name",
"Date",
Title,
Snippet,
Url,
Domain,
Sentiment,
"Page Type",
Language,
// "Country Code",
// "Continent Code",
Continent,
Country,
// "State Code",
// "County Code",
// "City Code",
Notes,
"Account Type",
Assignment,
"Author"
FROM [...];
Left Join (MyTable)
CalculationTable:
LOAD
Author,
if(TotalMentions <= 11, 'D',
if(TotalMentions > 11 and TotalMentions <= 24, 'C',
if(TotalMentions > 24 and TotalMentions <= 99, 'B',
if(TotalMentions > 99, 'A'
)))) as AuthorClass;
LOAD
Author,
Count(Author) as TotalMentions
Resident MyTable
Group By Author;
Can you post your script or an example qvw?
Here's the load script:
LOAD
"Query Id",
"Query Name",
"Date",
Title,
Snippet,
Url,
Domain,
Sentiment,
"Page Type",
Language,
// "Country Code",
// "Continent Code",
Continent,
Country,
// "State Code",
// "County Code",
// "City Code",
Notes,
"Account Type",
Assignment,
"Author",
This is the expression I used in a bar chart to show me the mentions by author for a group:
if( Count(Author)>99,
null(),
if( Count(Author)<25, null(),
Count(Author)))
I want to make this group a dimension.
try the following approach (you must edit FROM stetement with your source definition):
MyTable:
LOAD
"Query Id",
"Query Name",
"Date",
Title,
Snippet,
Url,
Domain,
Sentiment,
"Page Type",
Language,
// "Country Code",
// "Continent Code",
Continent,
Country,
// "State Code",
// "County Code",
// "City Code",
Notes,
"Account Type",
Assignment,
"Author"
FROM [...];
Left Join (MyTable)
CalculationTable:
LOAD
Author,
if(TotalMentions <= 11, 'D',
if(TotalMentions > 11 and TotalMentions <= 24, 'C',
if(TotalMentions > 24 and TotalMentions <= 99, 'B',
if(TotalMentions > 99, 'A'
)))) as AuthorClass;
LOAD
Author,
Count(Author) as TotalMentions
Resident MyTable
Group By Author;
You can use dimension AuthorClass in front end envirorment and apply, for example, the expression Count(Author) or Count(DISTINCT Author) to perform calculation.
That's great! Worked a treat. Cheers Carlo!
HI Tara - there is also a video on this topic here Qlik Sense - Dimension Grouping (video) that shows this process defined in the master items vs the load script:
That video is part of a subset of other videos that can help you will Qlik Sense.
Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.
Regards,
Mike Tarallo
Qlik