Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

getting top 1 count with Multi table value using set analysis

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.

Tags (2)
7 Replies
shraddha_g
Honored Contributor III

Re: Multi table value set analysis

Try with:

count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date =  {">=today()"}>}offer_id)

berryandcherry6
Contributor II

Re: getting top 1 count with Multi table value using set analysis

Hi Shraddha,

sorry, i have edited my query, its less than

count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date =  {"<=today()"}>}offer_id)

here it gives me offer_id count where for all expiration_date is less than today or today, But i need top one, which is nearest to today that is top 1.

shraddha_g
Honored Contributor III

Re: getting top 1 count with Multi table value using set analysis

Try

count({<log_type = {'33'},batch_meta_data_id = {'43'},expiration_date =  {"$(=max(expiration_date))"}>}offer_id)

jayaseelan
Contributor III

Re: Multi table value set analysis

Hi Supriya,

Can you please post your expecting output?

Thanks,

jayaseelan
Contributor III

Re: getting top 1 count with Multi table value using set analysis

We Can do this on Edit Script.

Please find the below attached .qvf file.

Hope this will helps you.

Thanks,

jayaseelan
Contributor III

Re: getting top 1 count with Multi table value using set analysis

Sorry

This is Updated Qvf

Thanks,

MVP
MVP

Re: getting top 1 count with Multi table value using set analysis

Something similar to the other post won't work here?

=FirstSortedValue({$<Batch_meta_data_id={'43'}, log_type = {'33'}, campaign_end_date = {"$(='<' & Date(Today()))"}>} Aggr(Sum({<Batch_meta_data_id={'43'}, log_type = {'33'}>} sign_up), campaign_end_date), -Aggr(campaign_end_date, campaign_end_date))