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

[berryandcherry6]

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.

[Anonymous]

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]

Hi bill.markham ,

Thanks for reply,

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

[maxgro · Accepted solution]

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