topic Re: expression for getting Campaign_id based on batch_id and date in QlikView

expression for getting Campaign_id based on batch_id and date

berryandcherry6 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: expression for getting Campaign_id based on batch_id and date

Gysbert_Wassenaar — Wed, 18 May 2016 10:45:01 GMT

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.

Re: expression for getting Campaign_id based on batch_id and date

berryandcherry6 — Wed, 18 May 2016 11:08:01 GMT

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.

Re: expression for getting Campaign_id based on batch_id and date

Gysbert_Wassenaar — Wed, 18 May 2016 11:50:56 GMT

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.

Re: expression for getting Campaign_id based on batch_id and date

Wed, 18 May 2016 13:12:23 GMT

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.

Re: expression for getting Campaign_id based on batch_id and date

berryandcherry6 — Fri, 20 May 2016 04:20:29 GMT

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.