topic Field not found but exists in database! in Connectivity & Data Prep

Field not found but exists in database!

Amba — Thu, 23 Dec 2021 13:45:33 GMT

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????

Re: Field not found but exists in database!

sunny_talwar — Tue, 08 Oct 2019 12:01:18 GMT

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

Re: Field not found but exists in database!

Amba — Tue, 08 Oct 2019 12:07:46 GMT

Hello Sunny,

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

Error: Field 'events.id' not found

Re: Field not found but exists in database!

sunny_talwar — Tue, 08 Oct 2019 12:18:05 GMT

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

Re: Field not found but exists in database!

Amba — Tue, 08 Oct 2019 12:29:09 GMT

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.

Re: Field not found but exists in database!

sunny_talwar — Tue, 08 Oct 2019 13:02:49 GMT

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?

Re: Field not found but exists in database!

Amba — Tue, 08 Oct 2019 13:08:40 GMT

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

Re: Field not found but exists in database!

martinpohl — Tue, 08 Oct 2019 13:11:02 GMT

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

Re: Field not found but exists in database!

martinpohl — Tue, 08 Oct 2019 13:12:27 GMT

also see here in the log

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

that your variable is not declared

Re: Field not found but exists in database!

sunny_talwar — Tue, 08 Oct 2019 13:30:18 GMT

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.

Re: Field not found but exists in database!

Amba — Tue, 08 Oct 2019 13:39:30 GMT

@martinpohl @sunny_talwar

Thank you guys for helping me.

I'm not sure how to proceed in order to select parts without using the load keyword, so I just commented my Loads and left the Select parts. Doing so I got an error telling me:

Error: QVX_UNEXPECTED_END_OF_DATA: ERROR [22007][Qlik][PostgreSQL](30) Error occured while trying to execute a query: [SQLState 22007] ERROR: invalid input syntax for type timestamp : »«
LINE 13: where odb_created_at > " ^

Here's my script, maybe you'll spot it right away, but I'm completely lost.

LIB CONNECT TO 'ipca (ilogs_amba)'; qualify *; //adds table name as prefix to all fields, * = for all tables LOAD id, //loads fields into the QlikSense repository system name, tenantid; [ipca_facility]: SELECT "id", "name", "tenantid" FROM "ipca"."ipca_facility"; Load id, name; [ipca_tenant]: Select "id", "name" from "ipca"."ipca_tenant"; LOAD id, definitiontypekey, definitiontypevisible, value, definitionid; [ipca_attribute]: SELECT "id", "definitiontypekey", "definitiontypevisible", "value", "definitionid" FROM "ipca"."ipca_attribute" Where definitiontypekey = 'PARKING_SPACES_OVERALL'; //only the "PARKING_SPACES_OVERALL" attributes are needed Unqualify *; //table-mapping start //maps the name of a object to its ID [ipca_facilityNameMapping]: Mapping Load ipca_facility.id, ipca_facility.name Resident ipca_facility; [ipca_facilityTenantidMapping]: Mapping Load ipca_facility.id, ipca_facility.tenantid Resident ipca_facility; [ipca_tenantMapping]: Mapping load ipca_tenant.id, ipca_tenant.name Resident ipca_tenant; //table-mapping end Qualify*; LOAD ApplyMap('ipca_facilityNameMapping', facilityid, Null()) as facilityid, //apply map on field attributeid; [ipca_facility_attribute]: SELECT "facilityid", "attributeid" FROM "ipca"."ipca_facility_attribute"; Unqualify*; [attributeidMapping]: Mapping load ipca_facility_attribute.facilityid, ipca_facility_attribute.attributeid Resident ipca_facility_attribute; [definitiontypekeyMapping]: Mapping Load ipca_attribute.id, ipca_attribute.definitiontypekey Resident ipca_attribute; LIB CONNECT TO 'ibirt (ilogs_amba)'; [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 ; 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)' ; else 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 ; 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 ; endif; [discounts]: LET dicountsexists=isnull(QvdCreateTime('lib://data (ilogs_amba)/discounts.qvd')); if $(dicountsexists) = 0 then LOAD * from [lib://data (ilogs_amba)/discounts.qvd] (qvd) ; Load [discounts.id], [discounts.discount_time], [discounts.count], [discounts.discount_unit], [discounts.discount_unit_name], ApplyMap('ticket_typeMapping', [discounts.ticket_type]) AS [discounts.ticket_type], [discounts.discount_type], [discounts.discount_name], discounts.discount_amount, [discounts.manufacturer], 'discount - '&Timestamp([discounts.discount_time])&discounts.ticket_type&discounts.carpark_id as link_datetime, ApplyMap('ipca_facilityNameMapping', discounts.carpark_id, null()) AS [discounts.carpark_id], ApplyMap('ipca_facilityTenantidMapping', discounts.carpark_id, null()) as discounts.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', discounts.carpark_id, null()), null()) as discounts.tenantname ; Concatenate SELECT "id" as "discounts.id", "discount_time" as "discounts.discount_time", "count" as "discounts.count", "discount_unit" as "discounts.discount_unit", "discount_unit_name" as "discounts.discount_unit_name", "ticket_type" "discounts.ticket_type", "discount_type" as "discounts.discount_type", "discount_name" as "discounts.discount_name", "discount_amount" as "discounts.discount_amount", "manufacturer" as "discounts.manufacturer", "carpark_id" as "discounts.carpark_id" FROM "public"."discounts" where odb_created_at > '$(lastexectime)' ; else Load [discounts.id], [discounts.discount_time], [discounts.count], [discounts.discount_unit], [discounts.discount_unit_name], ApplyMap('ticket_typeMapping', [discounts.ticket_type]) AS [discounts.ticket_type], [discounts.discount_type], [discounts.discount_name], discounts.discount_amount, [discounts.manufacturer], 'discount - '&Timestamp([discounts.discount_time])&discounts.ticket_type&discounts.carpark_id as link_datetime, ApplyMap('ipca_facilityNameMapping', discounts.carpark_id, null()) AS [discounts.carpark_id], ApplyMap('ipca_facilityTenantidMapping', discounts.carpark_id, null()) as discounts.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', discounts.carpark_id, null()), null()) as discounts.tenantname; SELECT "id" as "discounts.id", "discount_time" as "discounts.discount_time", "count" as "discounts.count", "discount_unit" as "discounts.discount_unit", "discount_unit_name" as "discounts.discount_unit_name", "ticket_type" "discounts.ticket_type", "discount_type" as "discounts.discount_type", "discount_name" as "discounts.discount_name", "discount_amount" as "discounts.discount_amount", "manufacturer" as "discounts.manufacturer", "carpark_id" as "discounts.carpark_id" FROM "public"."discounts"; endif; [revenues]: LET revenuesexists=isnull(QvdCreateTime('lib://data (ilogs_amba)/revenues.qvd')); if $(revenuesexists) = 0 then LOAD * from [lib://data (ilogs_amba)/revenues.qvd] (qvd) ; LOAD [revenues.id], revenues.payment_time, [revenues.count], ApplyMap('ticket_typeMapping', [revenues.ticket_type]) AS [revenues.ticket_type], [revenues.amount], revenues.payment_type, [revenues.manufacturer], ApplyMap('ipca_facilityNameMapping', revenues.carpark_id, null()) AS [revenues.carpark_id], ApplyMap('ipca_facilityTenantidMapping', revenues.carpark_id, null()) as revenues.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', revenues.carpark_id, null()), null()) as revenues.tenantname, revenues.ipcp_productid, revenues.net_amount, revenues.report_type, 'revenue - '&Timestamp([revenues.payment_time])&revenues.ticket_type&revenues.carpark_id as link_datetime ; qualify *; Concatenate SELECT "id" as "revenues.id", "payment_time" as "revenues.payment_time", "count" as "revenues.count", "ticket_type" as "revenues.ticket_type", "amount" as "revenues.amount", "payment_type" as "revenues.payment_type", "manufacturer" as "revenues.manufacturer", "carpark_id" as "revenues.carpark_id", "ipcp_productid" as "revenues.ipcp_productid", "net_amount" as "revenues.net_amount", "report_type" as "revenues.report_type" FROM "public"."revenues" where odb_created_at > '$(lastexectime)' ; unqualify*; else LOAD [revenues.id], revenues.payment_time, [revenues.count], ApplyMap('ticket_typeMapping', [revenues.ticket_type]) AS [revenues.ticket_type], [revenues.amount], revenues.payment_type, [revenues.manufacturer], ApplyMap('ipca_facilityNameMapping', revenues.carpark_id, null()) AS [revenues.carpark_id], ApplyMap('ipca_facilityTenantidMapping', revenues.carpark_id, null()) as revenues.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', revenues.carpark_id, null()), null()) as revenues.tenantname, revenues.ipcp_productid, revenues.net_amount, revenues.report_type, 'revenue - '&Timestamp([revenues.payment_time])&revenues.ticket_type&revenues.carpark_id as link_datetime ; SELECT "id" as "revenues.id", "payment_time" as "revenues.payment_time", "count" as "revenues.count", "ticket_type" as "revenues.ticket_type", "amount" as "revenues.amount", "payment_type" as "revenues.payment_type", "manufacturer" as "revenues.manufacturer", "carpark_id" as "revenues.carpark_id", "ipcp_productid" as "revenues.ipcp_productid", "net_amount" as "revenues.net_amount", "report_type" as "revenues.report_type" FROM "public"."revenues"; endif; [durations]: LET durationsexits=isnull(QvdCreateTime('lib://data (ilogs_amba)/durations.qvd')); if $(durationsexits) = 0 then LOAD * from [lib://data (ilogs_amba)/durations.qvd] (qvd); LOAD durations.id, durations.event_id_arrival, durations.event_id_departure, durations.event_time_arrival, durations.event_time_departure, durations.card_nr, ApplyMap('ticket_typeMapping', [durations.ticket_type]) as durations.ticket_type, durations.duration, durations.manufacturer, ApplyMap('ipca_facilityNameMapping', durations.carpark_id, null()) AS [durations.carpark_id], ApplyMap('ipca_facilityTenantidMapping', durations.carpark_id, null()) as durations.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', durations.carpark_id, null()), null()) as durations.tenantname, 'duration - '&Timestamp([durations.event_time_arrival])&Timestamp([durations.event_time_departure])&durations.ticket_type&durations.carpark_id as link_datetime ; SELECT "id" as "durations.id", "event_id_arrival" as "durations.event_id_arrival", "event_id_departure" as "durations.event_id_departure", "event_time_arrival" as "durations.event_time_arrival", "event_time_departure" as "durations.event_time_departure", "card_nr" as "durations.card_nr", "ticket_type" as "durations.ticket_type", "duration" as "durations.duration", "manufacturer" as "durations.manufacturer", "carpark_id" as "durations.carpark_id" FROM "public"."durations" where odb_created_at > '$(lastexectime)' ; else LOAD durations.id, durations.event_id_arrival, durations.event_id_departure, durations.event_time_arrival, durations.event_time_departure, durations.card_nr, ApplyMap('ticket_typeMapping', [durations.ticket_type]) as durations.ticket_type, durations.duration, durations.manufacturer, ApplyMap('ipca_facilityNameMapping', durations.carpark_id, null()) AS [durations.carpark_id], ApplyMap('ipca_facilityTenantidMapping', durations.carpark_id, null()) as durations.tenantid, ApplyMap('ipca_tenantMapping', ApplyMap('ipca_facilityTenantidMapping', durations.carpark_id, null()), null()) as durations.tenantname, 'duration - '&Timestamp([durations.event_time_arrival])&Timestamp([durations.event_time_departure])&durations.ticket_type&durations.carpark_id as link_datetime ; SELECT "id" as "durations.id", "event_id_arrival" as "durations.event_id_arrival", "event_id_departure" as "durations.event_id_departure", "event_time_arrival" as "durations.event_time_arrival", "event_time_departure" as "durations.event_time_departure", "card_nr" as "durations.card_nr", "ticket_type" as "durations.ticket_type", "duration" as "durations.duration", "manufacturer" as "durations.manufacturer", "carpark_id" as "durations.carpark_id" FROM "public"."durations"; endif; DateTimeLink: Load [events.event_time] as DateTime, link_datetime, [events.carpark_id] as [DateTimeLink.cp_id], events.ticket_type as DateTimeLink.ticket_type, events.tenantname as DateTimeLink.tenantname Resident events; load [discounts.discount_time] as DateTime, link_datetime, [discounts.carpark_id] as [DateTimeLink.cp_id], discounts.ticket_type as DateTimeLink.ticket_type, discounts.tenantname as DateTimeLink.tenantname Resident discounts; Load [revenues.payment_time] as DateTime, link_datetime, [revenues.carpark_id] as [DateTimeLink.cp_id], revenues.ticket_type as DateTimeLink.ticket_type, revenues.tenantname as DateTimeLink.tenantname Resident revenues; Load [durations.event_time_arrival] as DateTime, link_datetime, durations.carpark_id as DateTimeLink.cp_id, [durations.ticket_type] as [DateTimeLink.ticket_type], [durations.tenantname] as [DateTimeLink.tenantname] Resident durations; //call CalendarFromFieldWithTime('DateTime', 'CommonCalendarWithTime','CommonTime.'); Drop table ipca_facility; drop table ipca_tenant; //rename table ipca_attribute_temp to ipca_attribute0;

Re: Field not found but exists in database!

Amba — Wed, 09 Oct 2019 18:54:53 GMT

Still struggling here... (up)

Re: Field not found but exists in database!

Amba — Wed, 23 Oct 2019 07:30:44 GMT

I managed to make it work. Took a long time but at least it's working now.

In my script I have sections where I store my data in qvd. files.

Something like this:

LET hist_callexists=isnull(QvdCreateTime('lib://data (amba)/hist_call.qvd'));

I had to delete the files where my data was stored and load them again, it then worked