Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
berryandcherry6
Contributor II

expression for getting Campaign_id based on batch_id and date

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.

Tags (1)
5 Replies
MVP & Luminary
MVP & Luminary

Re: expression for getting Campaign_id based on batch_id and date

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.


talk is cheap, supply exceeds demand
berryandcherry6
Contributor II

Re: expression for getting Campaign_id based on batch_id and date

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.

MVP & Luminary
MVP & Luminary

Re: expression for getting Campaign_id based on batch_id and date

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.


talk is cheap, supply exceeds demand
Not applicable

Re: expression for getting Campaign_id based on batch_id and date

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
Contributor II

Re: expression for getting Campaign_id based on batch_id and date

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.