Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Shaglok
Contributor III
Contributor III

Above (or similar) question

Hi.

I am making an app using WhatsApp logs. So I have these fields:

 

  • conversationNum: the ID of the whole conversation
  • messageNum: the ID of each message of the conversations
  • Author: Bot, Person or Representative
  • messageData: the content of the messages

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.

Labels (3)
6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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:

  • When using AGGR(), any Set Analysis should be placed both inside and outside of the AGGR(). Usually it's not enough the use Set Analysis only outside of the AGGR. In my practice it produce wrong results. You may use Set Analysis before the whole formula, and then it will apply to everything, like this:

    {Set}  COUNT( AGGR ( ...) )

  • You should carefully formulate your condition. For example, your current condition {<messageData={"Yes"}>}  should filter out any other messages, including the message "did we solve...". You probably don't want that.
  • You are only interested in those answers "Yes" that come AFTER the question "did we solve...". So, I'd try to calculate the Message ID of that question, and then formulate the Set Analysis filter that filters messages with the Message ID that's greater than the questions' ID.
  • It might be easier and better for performance to calculate in the data load script, in a couple of Resident loads.

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,

Shaglok
Contributor III
Contributor III
Author

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.

  • I'll try with the SA in both sides of the formulla.
  • I want to count the number of "Yes" (and "No") that I have after that exact question, indeed. With the current formulla I get the total number of "Yes" in the conversation, so the issue is with the part that links both requisites. Both texts are sent by a bot, so they are exactly as I search.
  • I already have the messageID's. WhatsApp API send conversationID (something like 32jkllkjdsakjl3900opgsd) and conversationID (something like 32jkllkjdsakjl3900opgsd::msg0, 32jkllkjdsakjl3900opgsd:msg32). The thing is that the "Yes" or "No" am I looking for always in the next conversationID to the one of the question, but it's not always the +1 conversationID. Sometimes there is a "jump" and I am excluding from the charge some convers like voice notes or attachments without message, so I don't know if you still think I need that Residents in the charge script.

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 😞

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

Shaglok
Contributor III
Contributor III
Author

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;

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

 

 

 

 

Shaglok
Contributor III
Contributor III
Author

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!!