Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
basavannasv
Contributor
Contributor

Set analysis for embedded SQL query

Hi All,

Need help with set analysis for embedded SQL query. see below sample table and the queries..

Thanks for your help!


Table Name: ABCD

Conversation_idTimestampText
12:35Hi.asdf
13:45xyz
14:00Thanks.wer
24:15Hi.wert
24:20No
24:21Bye.111
34:30Hi.rytu
34:34Bye.111

#1 - How many records with Text "Hi…"?

SQL query : select count(Conversation_id) from ABCD where Text like 'Hi.%'

Set analysis expression : Count( {$<Text= {"Hi.*"}>} Conversation_id)

Result : 3

#2 - How many records with Text "Bye..."?

SQL query : select count(Conversation_id) from ABCD where Text like 'Bye.%'

Set analysis expression : Count( {$<Text= {"Bye.*"}>} Conversation_id)

Result : 2

#3 - How many records with Text "Hi…" and then followed by text "Thanks…" for a given conversation_id??

SQL query : select count (Distinct w1.Conversation_id) from ABCD w1 where Text like 'Hi.%' and w1.Conversation_id in (select w2.Conversation_id from ABCD w2 where w2.Text like 'Thanks.%' and w2.Timestamp > w1.Timestamp)

Set analysis expression : NEED HELP

Expected Result : 1

-Bas

1 Solution

Accepted Solutions
shraddha_g
Partner - Master III
Partner - Master III

7 Replies
ajsjoshua
Specialist
Specialist

Hi,

=count({<Conversation_id = p({<Text = {"Hi.*"}>}) * p({<Text = {"Bye.*"}>}) >} distinct Conversation_id)



Regards,

Joshua.

basavannasv
Contributor
Contributor
Author

‌Thanks for the response.

With the above set analysis, I am getting result as 2. But from the sample data, we should be expecting the result as 1. please see attached screenshot..

Also, with the real data i could see the value doubled with the same formula just like the sample result. what would be the reason?Set analysis.PNG

basavannasv
Contributor
Contributor
Author

appreciate if someone can look into this and provide some advise.

thanks

shraddha_g
Partner - Master III
Partner - Master III

PFA

muthukumar77
Partner - Creator III
Partner - Creator III

Try Like this,

ABCD:

LOAD * Inline [

Conversation_id, Timestamp, Text

1, 2:35, Hi.asdf

1, 3:45, xyz

1, 4:00, Thanks.wer

2, 4:15, Hi.wert

2, 4:20, No

2, 4:21, Bye.111

3, 4:30, Hi.rytu

3, 4:34, Bye.111

];

Join

LOAD Conversation_id,Timestamp as Timestamp1;

LOAD * Inline [

Conversation_id, Timestamp, Text

1, 2:35, Hi.asdf

1, 3:45, xyz

1, 4:00, Thanks.wer

2, 4:15, Hi.wert

2, 4:20, No

2, 4:21, Bye.111

3, 4:30, Hi.rytu

3, 4:34, Bye.111

];

Final:

LOAD Conversation_id,Timestamp,Text,

if(Text like 'Thanks.*' and Timestamp>Timestamp1,1,0) as ThanksFlag,

if(Text like 'Hi.*',1,0) as HiFlag

Resident ABCD;

DROP Table ABCD;

Expression like this:

Count(Distinct {<HiFlag={1},ThanksFlag={1}>}Conversation_id)

Muthukumar Pandiyan
basavannasv
Contributor
Contributor
Author

Thanks for your response.. the only thing missing is the timestamp condition in the query.  Could you help me on this as well?

select count (Distinct w1.Conversation_id) from ABCD w1 where Text like 'Hi.%' and w1.Conversation_id in (select w2.Conversation_id from ABCD w2 where w2.Text like 'Thanks.%' and w2.Timestamp > w1.Timestamp)

basavannasv
Contributor
Contributor
Author

this works thank you.