@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;
