Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

field not found: while using of different table column in new table.

Hi,

i have two tables

My requirement is to get rows from CAMPAIGNMETRICS  table where WHERE campaign_start_date >=Min_Date_Runrate and campaign_start_date <=      Max_Date_Runrate;

1.)

     Calendar1:

      LOAD

      Date($(#vCalendarStart) + RecNo()-1) AS "Date_Runrate"

      AutoGenerate $(#vCalendarLength);

 

MaxMinDate:

LOAD Date(Min(Date_Runrate)) as Min_Date_Runrate,

Date(Max(Date_Runrate)) as Max_Date_Runrate

Resident Calendar1 Where MonthPeriod_Runrate=Text(Date(WeekEnd(Today()), 'MMMYYYY'));

  

2.) CampaignMetric:

       LOAD `batch_meta_data_id`,

     `campaign_metrics_id` as Campaign_metrix_month

      Resident CAMPAIGNMETRICS WHERE campaign_start_date >=Min_Date_Runrate and campaign_start_date <=      Max_Date_Runrate;

Here when i load in script i get 'Field not found in campaignmetrics table'.  I do know that reason why it is giving error, because i am using Calendar1 table's column in CampaignMetric table where it doesn't contain.


Is it possible to get data like this in qliksense script or is there any other logic i could apply to get required result??


Any help is appreciated.



Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: field not found: while using of different table column in new table.

MaxMinDate:

LOAD Date(Min(Date_Runrate)) as Min_Date_Runrate,

Date(Max(Date_Runrate)) as Max_Date_Runrate

Resident Calendar1 Where MonthPeriod_Runrate=Text(Date(WeekEnd(Today()), 'MMMYYYY'));

add this

let mindate = Peek('Min_Date_Runrate');

let maxdate = Peek('Max_Date_Runrate');

and use the 2 new variables in the where (campaign_start_date should be a date)

CampaignMetric:

       LOAD `batch_meta_data_id`,

     `campaign_metrics_id` as Campaign_metrix_month

      Resident CAMPAIGNMETRICS

WHERE campaign_start_date >= '$(mindate)' and campaign_start_date <= '$(maxdate)';

3 Replies

Re: field not found: while using of different table column in new table.

How about using your date variables in the where clause, maybe something like :

WHERE campaign_start_date >= Date($(#vCalendarStart)

and campaign_start_date <=      Date ( ($(#vCalendarStart) + $(#vCalendarLength) ) ;

berryandcherry6
Contributor II

Re: field not found: while using of different table column in new table.

Hi bill.markham ,

Thanks for reply,

Could you check my updated query, where i have added MaxMinDate table

MVP
MVP

Re: field not found: while using of different table column in new table.

MaxMinDate:

LOAD Date(Min(Date_Runrate)) as Min_Date_Runrate,

Date(Max(Date_Runrate)) as Max_Date_Runrate

Resident Calendar1 Where MonthPeriod_Runrate=Text(Date(WeekEnd(Today()), 'MMMYYYY'));

add this

let mindate = Peek('Min_Date_Runrate');

let maxdate = Peek('Max_Date_Runrate');

and use the 2 new variables in the where (campaign_start_date should be a date)

CampaignMetric:

       LOAD `batch_meta_data_id`,

     `campaign_metrics_id` as Campaign_metrix_month

      Resident CAMPAIGNMETRICS

WHERE campaign_start_date >= '$(mindate)' and campaign_start_date <= '$(maxdate)';

Community Browser