7 Replies Latest reply: Oct 18, 2016 6:20 AM by Sunny Talwar RSS

    getting top 1 count with Multi table value using set analysis

    Supriya R

      Hi,

       

      I have a  camapign table with

       

      campaign_id, batch_meta_data_id, campaign_name,    start_date,       expiration_date

         2                         43                      camp1               10/07/2016         10/10/2016

      4                         43                      camp2               10/08/2016         17/10/2016

      5                         44                     camp3              20/10/2016         10/07/2016

      6                         43                      camp4               10/07/2016         10/13/2016

       

      offer table

       

      offer_id, client_id, batch_meta_data_id campaign_id

      2               3                 43                          2

      3               4                  43                         4

      4                4                  44                        5

      5                4                  43                         6

       

      log table

       

      offer id   log_type

      2                33

      2                34

      3                33

      4                33

      5                 33

      2                 33

       

      What i need :

          i need to get distinct count of offer id where expiration_date is nearest to today date(top 1) and less than today and log_type = 33 and batch_meta_data_id = 43.

       

      In sql query i get by this:

       

      SELECT count(Distinct(offer_id))  FROM LOG where offer_id IN (select offer_id from offer  where  campaign_id IN (SELECT campaign_id  FROM CAMPAIGN where expiration_date < NOW() and  batch_meta_data_id = 43 order by expiration_date) and log_type = 33

       

      How could i achieve this in Qliksense using set analysis.  Please help me on this.