topic Re: Loading error in Talend Studio

Loading error

sadeem — Mon, 22 Jul 2024 10:43:27 GMT

Hello All,

please i need your help as im facing below problem

"The following error occurred:

Field 'a' not found

The error occurred here:

TempCalendar: LOAD + Iterno()-1 As Num, Date( + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <="

when i try to load below script :

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min("DATA_DAY") as minDate,

max("DATA_DAY") as maxDate

Resident deactivation;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar_deactivation :

Load

TempDate AS "DATA_DAY",

date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as "Date",

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

// YeartoDate(TempDate)*-1 as CurYTDFlag,

// YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

// inmonth(today(),TempDate,0)*-1 as MTD_FLAG,

// InMonthToDate( TempDate,AddYears(($(vDateToday)),-1),0)*-1 as LYMTD_FLAG,

If( TempDate - YearStart(TempDate) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,

if( Day(TempDate)= today(),

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()),1,0) ,

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0)) as IsInMTD,

if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0) as IsInLYMTD,

// InMonthToDate(TempDate,$(vDateToday),-1,4)*-1 as LMTD_FLAG,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

// num(Year(TempDate) )+ num( Month(TempDate)) as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00') as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Day(TempDate),'00') as "MonthYearDay Num",

num(Year(TempDate) )& num( Month(TempDate),'00')& num( Week(TempDate),'00') as "MonthYearWeek Num",

num(Year(TempDate)-1 )& num( Month(TempDate),'00') & num( Day(TempDate),'00') as "LYMTD_Date_Num",

// floor( date#( date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'YYYYMMY'),'YYYYMMY')) as "MonthYear Num",

// ( date(TempDate, 'YYYYMM')) as "MonthYear Num",

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Join

load
1 as "MAx MTD",
date(max("Date"),'DD/MM/YYYY') as "MAX MTD Date",
"MonthYear Num"

Resident
MasterCalendar_deactivation
where IsInMTD = 1
Group by "MonthYear Num";

Drop Table TempCalendar;

store deactivation into [lib://Sadeem (lebara_qlik.admin)/deactivation.qvd](qvd);
store MasterCalendar_deactivation into [lib://Sadeem (lebara_qlik.admin)/MasterCalendar_deactivation .qvd](qvd);

exit script;

thanks in advance,

Re: Loading error

Tyrone2556 — Mon, 22 Jul 2024 13:53:00 GMT

Hi Sadeem, do you perhaps have an open Where clause in your resident load from table deactivation?

Re: Loading error

sadeem — Wed, 24 Jul 2024 12:25:07 GMT

Hello Tyrone2556,

no i dont have an open where

below my full script:

LOAD
Date(Date#(num(DATA_DAY),'YYYYMMDD'),'DD/MM/YYYY') as DATA_DAY,
IQAMA_TYPE,
ID_NUMBER,
SERV_NO,
USER_STATUS,
USER_CHANNEL,
ACTIVE_OPER_ID,
ACTIVE_LINE_OPERATOR,
CHURN_OPER_ID,
DEACTIVE_CHANNEL,
DEACTIVE_LINE_OPERATOR,
OWNER_TYPE,
DEALER_NAME,
ACTIVE_DEPT_ID,
CREATE_DATE,
ACTIVE_DATE,
EXPIRY_DATE,
VOICEPAYGO,
SMSPAYGO,
DATA_PAYGO,
CONT_PAYGO,
SUBSCRIPTIONS,
ONETIMEFEE,
TOTAL_REVENUE,
TOTAL_RECHARGE,
COUNT_RECHARGE,
REMARK,
REASON
FROM [lib://Sadeem (lebara_qlik.admin)/deactivation.qvd]
(qvd);

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min("DATA_DAY") as minDate,

max("DATA_DAY") as maxDate

Resident deactivation;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar_deactivation :

Load

TempDate AS "DATA_DAY",

date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as "Date",

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

// YeartoDate(TempDate)*-1 as CurYTDFlag,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Join

load
1 as "MAx MTD",
date(max("Date"),'DD/MM/YYYY') as "MAX MTD Date",
"MonthYear Num"

Resident
MasterCalendar_deactivation
where IsInMTD = 1
Group by "MonthYear Num";

Drop Table TempCalendar;

store deactivation into [lib://Sadeem (lebara_qlik.admin)/deactivation.qvd](qvd);
store MasterCalendar_deactivation into [lib://Sadeem (lebara_qlik.admin)/MasterCalendar_deactivation .qvd](qvd);

exit script;

Re: Loading error

anat — Wed, 24 Jul 2024 12:40:41 GMT

Seems varMaxDate variable is not having value,

Can you debug by executing the script till drop table temp

Re: Loading error

Tyrone2556 — Wed, 24 Jul 2024 12:41:23 GMT

Hi its the date format.

You need to set your date format at the start of the script.

SET DateFormat='DD/MM/YYYY';

This fixed it for me.