Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I am making an app using WhatsApp logs. So I have these fields:
I need a formula to count how many "yes" we receive just after the question "did we solve your issue?" I am using:
COUNT({<messageData={"Yes"}>} Aggr(If(messageData = '*did we solve*' and Above(Group) = 'Bot' and Grupo = 'Client', 1, 0), conversationId, messageId))
I receive results, but I know that they are not exact, because if I use a no existant question instad of "did we solve" I still receive results (the number of "Yes" in the whole conversation).
In the load script I have the information grouped by message and conversation id.
Hi there,
It's hard to troubleshoot the formula without playing with the actual data... I'll give you a few pointers to think about:
To learn more advanced development techniques, including advanced Set Analysis and AGGR, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!
Cheers,
Hi and thank you so much for your anwser.
I'm not at job yet, so I couldn't try it, but wanted to add a few things.
Regards and thanks again.
Update:
I added this to the load script:
[Order_messageID]:
NoConcatenate LOAD
messageId,
messageData
Resident [Convers]
order by messageId ASC;
But the formulla is still not working, and I am having a new Syn table. I think I am totally lost 😞
Hi there!
In your load script, I'd try the following steps:
1. Transform your raw data to the point of having the following fields, among other fields you may have (call the table "Conversations"):
ConversationID, such as 32jkllkjdsakjl3900opgsd
MessageID, such as 32jkllkjdsakjl3900opgsd::msg0
MessageNumber, such as 0 in this case
MessageData, such as "yes", "no", etc.
2. Calculate the earliest (or the latest?) Message Number of the bot question "did we solve..." and join the results to the original table Conversations:
left join (Conversations)
LOAD
ConversationID,
min(MessageNumber) as QuestionNumber
resident
Conversations
where
MessageData = 'Did we solve your problem?'
Group by
ConversationID
;
3. Calculate the number of "Yes" and "No" answers that appeared after the Question:
Answers:
LOAD
ConversationID,
sum(IF(UPPER(MessageData) = 'YES', 1, 0)) as YesAnswersCount,
sum(IF(UPPER(MessageData) = 'NO', 1, 0)) as NoAnswersCount
resident
Conversations
where
MessageNumber > QuestionNumber
group by
ConversationID
;
Something like this should give you the counts that you are looking for,
Cheers,
Wow! Thank you!
The only problem is (I think) with the sum. If in the conversation there are more "NO" after the question, it counts all of them. We have a succession of surveys depending on the response and I need to configure a counter for any survey.
How could we count only the first "NO" afther the question?
I also made a few changes to your script (that are working). I have a wildmatch to detect the survey text:
WildMatch(messageData, 'did we solve your problem?*') AS survey_1,
and then I use it in the left join
left join (Convers)
LOAD
// messageId,
conversationId,
min(MessageNumber) as survey1_message
resident
Convers
where
survey_1 = '1'
Group by
conversationId;
Hi Shaglok,
I'm glad that the suggested script works for you. Honestly, I think you might be getting deceiving results by counting any Yes or No answers after the bot's question. I think you should focus on the answer that comes immediately after the question. For example, the following conversation could possibly occur:
Bot: Did we solve your problem?
User: Not at all
Bot: So, do you still have a problem?
User: Yes
In this case, there was no specific "No" answer, and there was a "Yes" answer, but the problem wasn't solved.
I'll leave the actual logic development to you, but I'll just suggest a technical solution that allows you to determine the answer that is coming immediately after the question - using the function "Previous":
LOAD
...
IF(WildMatch(Previous(messageData), 'did we solve your problem?*') and left(upper(messageData), 3)='YES', 1, 0) as YesAnswer,
IF(WildMatch(Previous(messageData), 'did we solve your problem?*') and left(upper(messageData), 2)='NO', 1, 0) as NoAnswer,
...
Something like this... This load does not require a GROUP BY aggregation. The aggregation may come later, in one of next statements.
Cheers,
Thank you again, Oleg.
You are being extremely kind and helpful. I'll try this approach with an Order by instead the current group by's in the current Residents.
Thanks!!