Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Amba
Contributor II
Contributor II

Field not found but exists in database!

Hello guys, I've been dealing with this error now for a few days. It worked perfectly last week, but keeps giving me the same error now which is:

field events.id not found

 

Here's a bit of script:

[events]:
LET eventsexists=isnull(QvdCreateTime('lib://data (ilogs_amba)/events.qvd'));
if $(eventsexists) = 0 then
LOAD * from [lib://data (ilogs_amba)/events.qvd] (qvd);
LOAD
events.id,
events.odb_created_at,
[events.event_time],
APPLYMAP( 'events.device_typeMapping', [events.device_type]) as events.device_type,
APPLYMAP( 'events.event_typeMapping', [events.event_type]) as events.event_type,
ApplyMap('ticket_typeMapping', [events.ticket_type]) as events.ticket_type,
events.card_nr,
[events.count],
events.manufacturer,
'events - '&Timestamp([events.event_time])&events.ticket_type&events.carpark_id as link_datetime,
ApplyMap('ipca_facilityNameMapping', events.carpark_id, null()) as events.carpark_id,
ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', events.carpark_id, null()), null()) as events.tenantname
;
Concatenate select ERROR OCCURS HERE
"id" as "events.id",
"odb_created_at" as "events.odb_created_at",
"event_time" as "events.event_time",
"device_type" as "events.device_type",
"event_type" as "events.event_type",
"ticket_type" as "events.ticket_type",
"card_nr" as "events.card_nr",
"count" as "events.count",
"manufacturer" as "events.manufacturer",
"carpark_id" as "events.carpark_id"
from public.events
where odb_created_at > '$(lastexectime)'
;

The field exists in my database, I do not understand why qlik does not want to find it????

Labels (4)
12 Replies
sunny_talwar

Can you try to run your script without Concatenate

Concatenate select 
"id" as "events.id",
"odb_created_at" as "events.odb_created_at",
"event_time" as "events.event_time",
"device_type" as "events.device_type",
"event_type" as "events.event_type",
"ticket_type" as "events.ticket_type",
"card_nr" as "events.card_nr",
"count" as "events.count",
"manufacturer" as "events.manufacturer",
"carpark_id" as "events.carpark_id"
from public.events
where odb_created_at > '$(lastexectime)'
;

Amba
Contributor II
Contributor II
Author

Hello Sunny,

I have tried, unfortunately the same error happens with Select highlighted in red.

 

Error: Field 'events.id' not found

sunny_talwar

Would you be able to share the log file to see what is going on?

Amba
Contributor II
Contributor II
Author

I can't attach the files so I'll just copy the last log file here...

 

 

2019-10-08 12:17:58 Execution started.
2019-10-08 12:17:58 Qlik Sense Server Version 12.287.2
2019-10-08 12:17:58 CPU Target x64
2019-10-08 12:17:58 Operating System Windows Server 2019 Standard (64 bit edition)
2019-10-08 12:17:58 Wow64 mode Not using Wow64
2019-10-08 12:17:58 Language 0c07 Deutsch German
2019-10-08 12:17:58 Country AUT Österreich Austria
2019-10-08 12:17:58 MDAC Version 6.3.9600.16384
2019-10-08 12:17:58 MDAC Full Install Version 6.3.9600.16384
2019-10-08 12:17:58 PreferredCompression 2
2019-10-08 12:17:58 EnableParallelReload 1
2019-10-08 12:17:58 ParallelizeQvdLoads 1
2019-10-08 12:17:58 AutoSaveAfterReload 0
2019-10-08 12:17:58 BackupBeforeReload 0
2019-10-08 12:17:58 EnableFlushLog 0
2019-10-08 12:17:58 UserLogfileCharset 65001
2019-10-08 12:17:58 OdbcLoginTimeout -1
2019-10-08 12:17:58 OdbcConnectionTimeout -1
2019-10-08 12:17:58 LongestPossibleLine 1048576
2019-10-08 12:17:58 MaxCoreMask -1
2019-10-08 12:17:58 WebFileTimeout 0
2019-10-08 12:17:58 WebFileUserAgent Mozilla/5.0
2019-10-08 12:17:58 DontUseInternetCache 1
2019-10-08 12:17:58 ScriptWantsDbWrite false
2019-10-08 12:17:58 ScriptWantsExe false
2019-10-08 12:17:58 ICU_FOR_CHARACTER_CLASSIFICATION true
2019-10-08 12:17:58 ICU_FOR_CODE_PAGES true
2019-10-08 12:17:58 ICU_FOR_COLLATION true
2019-10-08 12:17:58 ICU_FOR_LOCALE_INFO true
2019-10-08 12:17:58 ICU_FOR_SUPPORT_INFO true
2019-10-08 12:17:58 ICU_FOR_TIME_ZONE_INFO true
2019-10-08 12:17:58 LogFile CodePage Used: 65001
2019-10-08 12:17:58 UseMemoryFillPattern 1
2019-10-08 12:17:58 ScriptMode Standard (limits system access)
2019-10-08 12:17:58 SequentialThreadCount 14
2019-10-08 12:17:58 NumberOfCores (effective) 4
2019-10-08 12:17:58 ReloadCodebase Bnf
2019-10-08 12:17:58 Reload Executed By UserDirectory=ILOGS; UserId=amba
2019-10-08 12:17:58 Process Executing Qlik Sense Server
2019-10-08 12:17:58 Process ID 6700
2019-10-08 12:17:58 Grpc version 5.0.0
2019-10-08 12:17:58 Protobuf version 3.4.0
2019-10-08 12:17:58 0002 let starttime = now()
2019-10-08 12:17:58 0004 SET ThousandSep=' '
2019-10-08 12:17:58 0005 SET DecimalSep=','
2019-10-08 12:17:58 0006 SET MoneyThousandSep='.'
2019-10-08 12:17:58 0007 SET MoneyDecimalSep=','
2019-10-08 12:17:58 0008 SET MoneyFormat='€ #.##0,00;-€ #.##0,00'
2019-10-08 12:17:58 0009 SET TimeFormat='hh:mm:ss'
2019-10-08 12:17:58 0010 SET DateFormat='DD.MM.YYYY'
2019-10-08 12:17:58 0011 SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]'
2019-10-08 12:17:58 0012 SET FirstWeekDay=0
2019-10-08 12:17:58 0013 SET BrokenWeeks=0
2019-10-08 12:17:58 0014 SET ReferenceDay=4
2019-10-08 12:17:58 0015 SET FirstMonthOfYear=1
2019-10-08 12:17:58 0016 SET CollationLocale='de-AT'
2019-10-08 12:17:58 0017 SET CreateSearchIndexOnReload=1
2019-10-08 12:17:58 0018 SET MonthNames='Jän.;Feb.;März;Apr.;Mai;Juni;Juli;Aug.;Sep.;Okt.;Nov.;Dez.'
2019-10-08 12:17:58 0019 SET LongMonthNames='Jänner;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember'
2019-10-08 12:17:58 0020 SET DayNames='Mo.;Di.;Mi.;Do.;Fr.;Sa.;So.'
2019-10-08 12:17:58 0021 SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag'
2019-10-08 12:17:58 0022 SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y'
2019-10-08 12:17:58 0023 Set dataManagerTables = '','mv_durationsperday','mv_eventsperday','mv_paymentsperday','mv_revenuesperday','mv_discountsperday','events','Evaluation Parameter','discounts','revenues'
2019-10-08 12:17:58 0026 For each name in '','mv_durationsperday','mv_eventsperday','mv_paymentsperday','mv_revenuesperday','mv_discountsperday','events','Evaluation Parameter','discounts','revenues'
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0027 Let index = 0
2019-10-08 12:17:58 0028 Let currentName = name
2019-10-08 12:17:58 0029 Let tableNumber = TableNumber(name)
2019-10-08 12:17:58 0030 Let matches = 0
2019-10-08 12:17:58 0031 Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
2019-10-08 12:17:58 0037 If index > 0 then
2019-10-08 12:17:58 0040 Next
2019-10-08 12:17:58 0041 Set dataManagerTables =
2019-10-08 12:17:58 0046 [event_typeMapping]:
2019-10-08 12:17:58 0047 MAPPING LOAD * INLINE
2019-10-08 12:17:58 0048 [
2019-10-08 12:17:58 0049 event_typeMapping-FROM,event_typeMapping-TO
2019-10-08 12:17:58 0050 2,drive-through
2019-10-08 12:17:58 0051 3,manual-opening
2019-10-08 12:17:58 0052 27,cash-door closed
2019-10-08 12:17:58 0053 38,cash-door opened
2019-10-08 12:17:58 0054 12,drive-through without 'ISO-card number'
2019-10-08 12:17:58 0055 13,manual-opening without 'ISO-card number'
2019-10-08 12:17:58 0056 4,lock
2019-10-08 12:17:58 0057 ]
2019-10-08 12:17:58 2 fields found: event_typeMapping-FROM, event_typeMapping-TO,
2019-10-08 12:17:58 7 lines fetched
2019-10-08 12:17:58 0059 [device_typeMapping]:
2019-10-08 12:17:58 0060 MAPPING LOAD * INLINE
2019-10-08 12:17:58 0061 [
2019-10-08 12:17:58 0062 device_typeMapping-FROM,device_typeMapping-TO
2019-10-08 12:17:58 0063 2,Exit Device
2019-10-08 12:17:58 0064 1,Entry Device
2019-10-08 12:17:58 0065 5,Self-Cashout
2019-10-08 12:17:58 0066 ]
2019-10-08 12:17:58 2 fields found: device_typeMapping-FROM, device_typeMapping-TO,
2019-10-08 12:17:58 3 lines fetched
2019-10-08 12:17:58 0068 [ticket_typeMapping]:
2019-10-08 12:17:58 0069 Mapping Load * Inline
2019-10-08 12:17:58 0070 [
2019-10-08 12:17:58 0071 from, to
2019-10-08 12:17:58 0072 1, short-term
2019-10-08 12:17:58 0073 2, Presale Ticket (PrePaid)
2019-10-08 12:17:58 0074 4, Kongress-Ticket
2019-10-08 12:17:58 0075 5, Lost Ticket
2019-10-08 12:17:58 0076 6, One-use ticket
2019-10-08 12:17:58 0077 7,Replacement ticket
2019-10-08 12:17:58 0078 11, Contract Parker
2019-10-08 12:17:58 0079 12, Function card
2019-10-08 12:17:58 0080 13, seasonal
2019-10-08 12:17:58 0081 14, Value Card / Debit Card (PrePaid)
2019-10-08 12:17:58 0082 15, Time Cheque (PrePaid)
2019-10-08 12:17:58 0083 16, Bresica-Value-Card/Bresica-Season-Parker-Card
2019-10-08 12:17:58 0084 20, EC-Card
2019-10-08 12:17:58 0085 21, Credit Card
2019-10-08 12:17:58 0086 33, not identifiable
2019-10-08 12:17:58 0087 ]
2019-10-08 12:17:58 2 fields found: from, to,
2019-10-08 12:17:58 15 lines fetched
2019-10-08 12:17:58 0089 [payment_typeMapping]:
2019-10-08 12:17:58 0090 Mapping Load * Inline
2019-10-08 12:17:58 0091 [
2019-10-08 12:17:58 0092 nr, type
2019-10-08 12:17:58 0093 0,Bar-Zahlung
2019-10-08 12:17:58 0094 1,Kreditkarte
2019-10-08 12:17:58 0095 2,Scheckbezahlung an MPS (Manual Payment Station/Handkasse) / Cheque
2019-10-08 12:17:58 0096 3,Ext. Kreditkarten an MPS (Manual Payment Station/Handkasse)
2019-10-08 12:17:58 0097 4,Nullbezahlung an MPS (Manual Payment Station/Handkasse) / Zeroticket
2019-10-08 12:17:58 0098 5,Wertscheck / Value Cheque
2019-10-08 12:17:58 0099 5,Zeitscheck / Time Cheque
2019-10-08 12:17:58 0100 6,EC-Card / Zeitscheck / Time Cheque
2019-10-08 12:17:58 0101 ]
2019-10-08 12:17:58 2 fields found: nr, type,
2019-10-08 12:17:58 8 lines fetched
2019-10-08 12:17:58 0103 [events.device_typeMapping]:
2019-10-08 12:17:58 0104 MAPPING LOAD * INLINE
2019-10-08 12:17:58 0105 [
2019-10-08 12:17:58 0106 events.device_typeMapping-FROM,events.device_typeMapping-TO
2019-10-08 12:17:58 0107 2,Exit Device
2019-10-08 12:17:58 0108 1,Entry Device
2019-10-08 12:17:58 0109 5,Self-Checkout
2019-10-08 12:17:58 0110 ]
2019-10-08 12:17:58 2 fields found: events.device_typeMapping-FROM, events.device_typeMapping-TO,
2019-10-08 12:17:58 3 lines fetched
2019-10-08 12:17:58 0112 [events.event_typeMapping]:
2019-10-08 12:17:58 0113 MAPPING LOAD * INLINE
2019-10-08 12:17:58 0114 [
2019-10-08 12:17:58 0115 events.event_typeMapping-FROM,events.event_typeMapping-TO
2019-10-08 12:17:58 0116 2,drive-through
2019-10-08 12:17:58 0117 3,manual opening
2019-10-08 12:17:58 0118 ]
2019-10-08 12:17:58 2 fields found: events.event_typeMapping-FROM, events.event_typeMapping-TO,
2019-10-08 12:17:58 2 lines fetched
2019-10-08 12:17:58 0120 MLS:
2019-10-08 12:17:58 0121
2019-10-08 12:17:58 0122 First 15 LOAD
2019-10-08 12:17:58 0123 Title,
2019-10-08 12:17:58 0124 Language,
2019-10-08 12:17:58 0125 Value
2019-10-08 12:17:58 0126 FROM [lib://translation (ilogs_amba)/spms_mls.xlsx]
2019-10-08 12:17:58 0127 (ooxml, embedded labels, table is TRSL)
2019-10-08 12:17:58 3 fields found: Title, Language, Value,
2019-10-08 12:17:58 15 lines fetched
2019-10-08 12:17:58 0139 LIB CONNECT TO 'ipca (ilogs_amba)'
2019-10-08 12:17:59 Connected.
2019-10-08 12:17:59 0141 qualify *
2019-10-08 12:17:59 0143 LOAD id,
2019-10-08 12:17:59 0144 name,
2019-10-08 12:17:59 0145 tenantid
2019-10-08 12:17:59 0146 [ipca_facility]:
2019-10-08 12:17:59 0147 SELECT "id",
2019-10-08 12:17:59 0148 "name",
2019-10-08 12:17:59 0149 "tenantid"
2019-10-08 12:17:59 0150 FROM "ipca"."ipca_facility"
2019-10-08 12:18:00 3 fields found: ipca_facility.id, ipca_facility.name, ipca_facility.tenantid,
2019-10-08 12:18:00 255 lines fetched
2019-10-08 12:18:00 0152 Load id, name
2019-10-08 12:18:00 0153 [ipca_tenant]:
2019-10-08 12:18:00 0154 Select "id", "name" from "ipca"."ipca_tenant"
2019-10-08 12:18:00 2 fields found: ipca_tenant.id, ipca_tenant.name,
2019-10-08 12:18:00 14 lines fetched
2019-10-08 12:18:00 0156 LOAD id,
2019-10-08 12:18:00 0157 definitiontypekey,
2019-10-08 12:18:00 0158 definitiontypevisible,
2019-10-08 12:18:00 0159 value,
2019-10-08 12:18:00 0160 definitionid
2019-10-08 12:18:00 0161 [ipca_attribute]:
2019-10-08 12:18:00 0162 SELECT "id",
2019-10-08 12:18:00 0163 "definitiontypekey",
2019-10-08 12:18:00 0164 "definitiontypevisible",
2019-10-08 12:18:00 0165 "value",
2019-10-08 12:18:00 0166 "definitionid"
2019-10-08 12:18:00 0167 FROM "ipca"."ipca_attribute"
2019-10-08 12:18:00 0168 Where definitiontypekey = 'PARKING_SPACES_OVERALL'
2019-10-08 12:18:00 5 fields found: ipca_attribute.id, ipca_attribute.definitiontypekey, ipca_attribute.definitiontypevisible, ipca_attribute.value, ipca_attribute.definitionid,
2019-10-08 12:18:00 117 lines fetched
2019-10-08 12:18:00 0169 Unqualify *
2019-10-08 12:18:00 0173 [ipca_facilityNameMapping]:
2019-10-08 12:18:00 0174 Mapping Load ipca_facility.id, ipca_facility.name
2019-10-08 12:18:00 0175 Resident ipca_facility
2019-10-08 12:18:00 2 fields found: ipca_facility.id, ipca_facility.name,
2019-10-08 12:18:00 255 lines fetched
2019-10-08 12:18:00 0177 [ipca_facilityTenantidMapping]:
2019-10-08 12:18:00 0178 Mapping Load ipca_facility.id, ipca_facility.tenantid
2019-10-08 12:18:00 0179 Resident ipca_facility
2019-10-08 12:18:00 2 fields found: ipca_facility.id, ipca_facility.tenantid,
2019-10-08 12:18:00 255 lines fetched
2019-10-08 12:18:00 0181 [ipca_tenantMapping]:
2019-10-08 12:18:00 0182 Mapping load ipca_tenant.id, ipca_tenant.name
2019-10-08 12:18:00 0183 Resident ipca_tenant
2019-10-08 12:18:00 2 fields found: ipca_tenant.id, ipca_tenant.name,
2019-10-08 12:18:00 14 lines fetched
2019-10-08 12:18:00 0186 Qualify*
2019-10-08 12:18:00 0187 LOAD ApplyMap('ipca_facilityNameMapping', facilityid, Null()) as facilityid,
2019-10-08 12:18:00 0188 attributeid
2019-10-08 12:18:00 0189 [ipca_facility_attribute]:
2019-10-08 12:18:00 0190 SELECT "facilityid",
2019-10-08 12:18:00 0191 "attributeid"
2019-10-08 12:18:00 0192 FROM "ipca"."ipca_facility_attribute"
2019-10-08 12:18:00 2 fields found: ipca_facility_attribute.facilityid, ipca_facility_attribute.attributeid,
2019-10-08 12:18:00 2 549 lines fetched
2019-10-08 12:18:00 0193 Unqualify*
2019-10-08 12:18:00 0195 [attributeidMapping]:
2019-10-08 12:18:00 0196 Mapping load ipca_facility_attribute.facilityid, ipca_facility_attribute.attributeid
2019-10-08 12:18:00 0197 Resident ipca_facility_attribute
2019-10-08 12:18:00 2 fields found: ipca_facility_attribute.facilityid, ipca_facility_attribute.attributeid,
2019-10-08 12:18:00 2 549 lines fetched
2019-10-08 12:18:00 0199 [definitiontypekeyMapping]:
2019-10-08 12:18:00 0200 Mapping Load ipca_attribute.id, ipca_attribute.definitiontypekey
2019-10-08 12:18:00 0201 Resident ipca_attribute
2019-10-08 12:18:00 2 fields found: ipca_attribute.id, ipca_attribute.definitiontypekey,
2019-10-08 12:18:00 117 lines fetched
2019-10-08 12:18:00 0203 LIB CONNECT TO 'ibirt (ilogs_amba)'
2019-10-08 12:18:01 Connected.
2019-10-08 12:18:01 0205 [events]:
2019-10-08 12:18:01 0206 LET eventsexists=isnull(QvdCreateTime('lib://data (ilogs_amba)/events.qvd'))
2019-10-08 12:18:01 0207 if 0 = 0 then
2019-10-08 12:18:01 0208 LOAD * from [lib://data (ilogs_amba)/events.qvd] (qvd)
2019-10-08 12:18:01 12 fields found: events.id, events.odb_created_at, events.event_time, events.device_type, events.event_type, events.ticket_type, events.card_nr, events.count, events.manufacturer, link_datetime, events.carpark_id, events.tenantname,
2019-10-08 12:18:01 100 lines fetched
2019-10-08 12:18:01 0209 Load
2019-10-08 12:18:01 0210 events.id,
2019-10-08 12:18:01 0211 events.odb_created_at,
2019-10-08 12:18:01 0212 [events.event_time],
2019-10-08 12:18:01 0213 APPLYMAP( 'events.device_typeMapping', [events.device_type]) as events.device_type,
2019-10-08 12:18:01 0214 APPLYMAP( 'events.event_typeMapping', [events.event_type]) as events.event_type,
2019-10-08 12:18:01 0215 ApplyMap('ticket_typeMapping', [events.ticket_type]) as events.ticket_type,
2019-10-08 12:18:01 0216 events.card_nr,
2019-10-08 12:18:01 0217 [events.count],
2019-10-08 12:18:01 0218 events.manufacturer,
2019-10-08 12:18:01 0219 'events - '&Timestamp([events.event_time])&events.ticket_type&events.carpark_id as link_datetime,
2019-10-08 12:18:01 0220 ApplyMap('ipca_facilityNameMapping', events.carpark_id, null()) as events.carpark_id,
2019-10-08 12:18:01 0221 ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', events.carpark_id, null()), null()) as events.tenantname
2019-10-08 12:18:01 0223 Select
2019-10-08 12:18:01 0224 "id" as "events.id",
2019-10-08 12:18:01 0225 "odb_created_at" as "events.odb_created_at",
2019-10-08 12:18:01 0226 "event_time" as "events.event_time",
2019-10-08 12:18:01 0227 "device_type" as "events.device_type",
2019-10-08 12:18:01 0228 "event_type" as "events.event_type",
2019-10-08 12:18:01 0229 "ticket_type" as "events.ticket_type",
2019-10-08 12:18:01 0230 "card_nr" as "events.card_nr",
2019-10-08 12:18:01 0231 "count" as "events.count",
2019-10-08 12:18:01 0232 "manufacturer" as "events.manufacturer",
2019-10-08 12:18:01 0233 "carpark_id" as "events.carpark_id"
2019-10-08 12:18:01 0234 from public.events
2019-10-08 12:18:01 0235 where odb_created_at > ''
2019-10-08 12:18:02 Error: Field 'events.id' not found
2019-10-08 12:18:02 Execution Failed
2019-10-08 12:18:02 Execution finished.

sunny_talwar

Can you try this for testing

select 
"id" as "events.id",
"odb_created_at" as "events.odb_created_at",
"event_time" as "events.event_time",
"device_type" as "events.device_type",
"event_type" as "events.event_type",
"ticket_type" as "events.ticket_type",
"card_nr" as "events.card_nr",
"count" as "events.count",
"manufacturer" as "events.manufacturer",
"carpark_id" as "events.carpark_id"
from public.events
where odb_created_at > '$(lastexectime)'

when you load this, do you see a field events.id shown within the dashboard?

Amba
Contributor II
Contributor II
Author

That does not work, same error again.
I do see them since my app used to work last week, but the script stopped working today for some reason. I have no idea why. Everything else works but the parts where i'm defining things. I changed nothing in the databse nor the script.

I do not know if you do that, but maybe you'd like to have a look via teamviewer?

Regards,
Ahmed

martinpohl
Partner
Partner

Hello,

try only to select your datas from database without a load - statement to see the fieldnames from database.

In some cases, the fieldnames are in upper letters (EVENTS.ID instead od events.id) although it is scripted in lower letters.

Regards

martinpohl
Partner
Partner

also see here in the log

2019-10-08 12:18:01 0235 where odb_created_at > ''

that your variable is not declared

sunny_talwar

Like @martinpohl  said, run just the select part of your script without the Load part.... we are doing this to check the name of the field... is it events.id or EVENTS.ID or just id for some reason. Once we know what the SELECT is loading, we can call it using the same name in the LOAD part of the script.