Field not found

[filipborgers]

Hi all

I receive a "Field not found" error in my load script.

I have different sections and in my "Job"-section I inserted an IF statement to create a new field with the name "ZelfCheckJobDescr".  In the next section I inserted another IF statement in which I use the field "ZelfCheckJobDescr". But when this is loaded, I receive the error "Field 'ZelfCheckJobDescr' not found". 

What can be the problem?

Tx!

[juliensaintmaxent3113]

Hi,

can you send me screenshot of your script ?

Thanks

Bye

[y_grynechko]

Can you paste complete if that you use? And the name of the column you want to base it on(name from database). 

[filipborgers]

This is my "Job"-section:

LIB CONNECT TO 'lsqlp_ultimo (uzbrussel_filip)';

Job:
LOAD JobId,
JobBofId as BofId,
JobRecStatus,
JobTargetDate,
JobReportDate,
JobActiveDate,
JobFinishDate,
JobContext,
JobAcceptDate,
JobOpenDate,
"_JOBSURVCREATED",
"_JOBSURVFINISHED",
JobDuration,
"_JobContactName",
"_JobContactPhone",
"_JobEqmUsage",
"_JobEqmUsageAttention",
"_JobImpactLabo",
"_JobImpactLaboAct",
"_joblocation",
"_JobMDgas",
"_JOBPCHEMPID",
"_JobPurchaseNo",
"_JOBTOPDESKJOBID",
"_JOBTOPDESKPROJECTLEIDERTD",
"_JOBTOPDESKPROJECTNAAM",
"_JOBTOPDESKPROJECTNUMMER",
JobDepId as DepId,
JobDescr,
IF(MATCH(JobDescr,'Zelfcheck: Defecten aan deuren of omlijsting.','Zelfcheck: Zijn er voldoende stopcontacten, zodat men een goed kabelmanagement heeft?','Zelfcheck: Plafonds bijv. Vuil, ontbrekend,losliggend,...','Zelfcheck: Defecten aan deuren of omlijsting.','Zelfcheck: Beschadiging muren en vloeren',
'Zelfcheck: Defecten aan ramen, zonnewering en muggenramen','Zelfcheck: Defecten aan sloten en badgecontrole','Zelfcheck: Gebreken badkamer en sanitair',
'Zelfcheck: Hangt op elke geïdentificeerde plaats een brandblustoestel?','Zelfcheck: Lokaalidentificatie, signalisatie en veiligheidssignalisatie niet meer aanwezig',
'Zelfcheck: Defect aan bestaande stopcontacten, lichtschakelaars, data-outlets,…','Zelfcheck: Defecte verlichting','Zelfcheck: Fluitend geluid aan ventielen medische gassen (teken van een lek)'),'ZelfcheckJob') AS ZelfCheckJobDescr,
JobDocId,
JobEmpId,
JobEmpId as JobBehandelaar,
JobEqmId as EqmId,
JobEqmtId,
JobFeedbackText,
JobFinishEmpId,
JobKwsId AS KwsId,
JobPriId,
JobRemarkText1,
JobReportEmpId,
JobReportFKEmpId as JobMelder,
JobReportText,
JobSdrtId,
JobSkcId as SkcId,
JobSpcId as SpcId,
JobSvcId,
JobText,
JobVdrId,
JobWotId as WotId,
1 as JobCounter;
SQL SELECT JobId,
JobBofId,
JobRecStatus,
JobTargetDate,
JobReportDate,
JobActiveDate,
JobFinishDate,
JobContext,
JobAcceptDate,
JobOpenDate,
"_JOBSURVCREATED",
"_JOBSURVFINISHED",
JobDuration,
"_JobContactName",
"_JobContactPhone",
"_JobEqmUsage",
"_JobEqmUsageAttention",
"_JobImpactLabo",
"_JobImpactLaboAct",
"_joblocation",
"_JobMDgas",
"_JOBPCHEMPID",
"_JobPurchaseNo",
"_JOBTOPDESKJOBID",
"_JOBTOPDESKPROJECTLEIDERTD",
"_JOBTOPDESKPROJECTNAAM",
"_JOBTOPDESKPROJECTNUMMER",
JobDepId,
JobDescr,
JobDocId,
JobEmpId,
JobEqmId,
JobEqmtId,
JobFeedbackText,
JobFinishEmpId,
JobKwsId,
JobPriId,
JobRemarkText1,
JobReportEmpId,
JobReportFKEmpId,
JobReportText,
JobSdrtId,
JobSkcId,
JobSpcId,
JobSvcId,
JobText,
JobVdrId,
JobWotId
FROM Ultimo.dba.Job;

and this is my "WorkOrderType"-section:

LIB CONNECT TO 'lsqlp_ultimo (uzbrussel_filip)';
UNQUALIFY *;

WorkorderType:
LOAD WotRecStatus,
WotContext,
WotEqmNotAvailable,
WotMTPrevinspection,
WotMTCormaintenance,
WotRecCreateDate,
WotAllowsRelease,
WotDescr1,
IF(ZelfCheckJobDescr='ZelfcheckJob','ZelfcheckJob',IF(WILDMATCH(WotDescr1,'Periodiek*')=1,'Periodieke onderhoudsjobs','Correctieve jobs')) AS JobCategorie,
WotHourId,
WotId,
WotSchedularColor,
WotCategory;
SQL SELECT WotRecStatus,
WotContext,
WotEqmNotAvailable,
WotMTPrevinspection,
WotMTCormaintenance,
WotRecCreateDate,
WotAllowsRelease,
WotDescr1,
WotHourId,
WotId,
WotSchedularColor,
WotCategory
FROM Ultimo.dba.WorkOrderType;

[juliensaintmaxent3113]

Hi,

your field ZelfCheckJobDescr was created in job table but if you want to use this field in the table workordertype, you must use a join.

your table  Ultimo.dba.WorkOrderType hasn't got  the field ZelfCheckJobDescr so qlik shows error.

I hope my answer is clear.

Bye

[y_grynechko]

There is no ZelfCheckJobDescr in WorkorderType. 

Join is one of the options, or you can use applymap. 

Do something like that: 

After you load job table create mapping:

MapZelfCheckJobDescrtoWorkorderType :
Mapping LOAD WotId, ZelfCheckJobDescr Resident Job;

After that in your if statement you can use mapping, like here: 

https://community.qlik.com/t5/New-to-QlikView/Using-ApplyMap-with-Conditional-If-statement/td-p/8376...