expression for getting Campaign_id based on batch_id and date

[berryandcherry6]

LOAD `campaign_id` as  Campaign_ID,

`batch_meta_data_id` as Batch_ID,

    `campaign_name`,

    date(floor(start_date),'MM-DD-YYYY') as current_start_date,

    `expiration_date`;

SQL SELECT

`batch_meta_data_id`,

`campaign_id`,

    `campaign_name`,

    `start_date`,

    `expiration_date`

FROM CAMPAIGN ;

CurrentCampaignData:

Load

Batch_ID ,

Campaign_ID as Current_Campaign_ID

Resident CAMPAIGN Where (current_start_date = Date(weekstart(Num(today()))+1));

From CurrentCampaignData table i have to select Current_Campaign_ID where batch_id = 23,24 and current_start_date = Date(weekstart(Num(today())+1)).

Here my requirement is to get Campaign_ID for batch_id = 23,24 and current_start_date = Date(weekstart(Num(today())+1)) from CAMPAIGN table.

I have to apply this in expression in front end for table chart to retrieve campaign_id

How could i achieve this.Please help me in this regard.

[Gysbert_Wassenaar]

Changing the expression(s) in the chart from something like Count(Campaign_ID) to Count({<Batch_ID={23,24},current_start_date={'$(=Date(weekstart(Num(today())+1))'}>}Campaign_ID) might work. Make sure to use the correct case sensitive field names. For example current_start_date and current_start_Date are two different names.

[berryandcherry6]

Hi gwassenaar ,

Its not bar chart,its table i am using

Dimension is:

MetricsDims:

LOAD * INLINE [

      Metrics

     Invitations

     Landing Page Hits

     Sign-Ups

     Partial Completions

    Completions

    Completions Rate

];

Expression:

Num(if(Metrics='Invitations',count({$<DateType= {'invitation'},client_name = {'maxicon'}>} invitation_id),

if(Metrics='Landing Page Hits', count({$<CanonicalDate={">=$(=date(max(weekstart)))<=$(=date(max(WeekEnd)))"},

DateType= {'accessed'},client_name = {'maxicon'}>} invitation_id),

if(Metrics='Sign-Ups',sum({$<DateType= {'signed'},client_name = {'maxicon'}>} user_signed),

if(Metrics='Completions',sum({$< DateType= {'completion'},client_name = {'maxicon'},final_test_status={'passed'}>} course_completed),

if(Metrics='Completions Rate',sum({$<DateType= {'completion'},client_name = {'maxicon'},final_test_status={'passed'}>} course_completed)/

count({$<DateType= {'invitation'},client_name = {'maxicon'}>} invitation_id)

))))), if(Metrics='Completions Rate','#,##0.0%', '#,##0'))

For this i have to add campaign_id to get count for that particular id.

[Gysbert_Wassenaar]

Add this to all the set modifiers: Batch_ID={23,24},current_start_date={'$(=Date(weekstart(Num(today())+1))'}

If it doesn't work post a small qlikview document that demonstrates the problem.

[#5]

Supriya, you can add flag to the your table to identify the current campaign details.

LOAD `campaign_id` as  Campaign_ID,

`batch_meta_data_id` as Batch_ID,

    `campaign_name`,

    `expiration_date`,

     if(Match(batch_meta_data_id,23,24) and Floor(expiration_date) = floor(weekstart(Num(today()))+1) , 1, 0) as CurrentBatchFlag

;

SQL SELECT

`batch_meta_data_id`,

`campaign_id`,

    `campaign_name`,

    `start_date`,

    `expiration_date`

FROM CAMPAIGN ;

On UI, add the CurrentBatchFlag ={1} to your existing set expressions.

[berryandcherry6]

Hi dathu.qv,

Thanks for reply

Can i put this logic in form of expression in front end. because i wont be able to do this in script as different client will be having different batch_meta_data_id. so it will be troublesome.