7 Replies Latest reply: Mar 15, 2017 8:34 PM by Basavanna SV RSS

    Set analysis for embedded SQL query

    Basavanna SV

      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