Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
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