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: 
berryandcherry6
Creator II
Creator 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.

7 Replies
shraddha_g
Partner - Master III
Partner - Master III

Try with:

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

berryandcherry6
Creator II
Creator II
Author

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
Partner - Master III
Partner - Master III

Try

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

jayaseelan
Creator III
Creator III

Hi Supriya,

Can you please post your expecting output?

Thanks,

jayaseelan
Creator III
Creator III

We Can do this on Edit Script.

Please find the below attached .qvf file.

Hope this will helps you.

Thanks,

jayaseelan
Creator III
Creator III

Sorry

This is Updated Qvf

Thanks,

sunny_talwar

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