Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 (1)
12 Replies
Amba
Contributor II
Contributor II
Author

@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;
Amba
Contributor II
Contributor II
Author

Still struggling here... (up)

Amba
Contributor II
Contributor II
Author

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