Att läsa data från en Tesla

Tesla har många moderna funktioner i sina bilar och en av dessa är att många av bilens funktioner och data finns tillgängliga i en mobilapp. Mobilappen kommunicerar med Teslas servrar via ett REST-API som också går att använda med andra mjukvaror - som Qlik Sense. I det här inlägget ska vi gå igenom hur man kan läsa ut data från en Tesla med hjälp av Qlik Sense.



Detta inlägg blir ganska långt för att beskriva alla detaljer av hur man kan bygga en applikation. Jag har förberett en applikation som du också kan ladda ner här om du inte vill sätta upp allt själv:

Tesla Example
.zip
ZIP • 310KB

Temat till appen:

TeslaTheme
.zip
ZIP • 1KB

För Tesla-logotypen använder jag denna extension:

https://github.com/ralfbecher/QlikSense_Extension_Image


Hämta en authorization token

En vanlig teknik med REST-API:r är att först hämta en authorization token som man använder vidare i nya anrop. För att hämta ut Teslas authorization token behöver man fyra olika värden:

  • Mailadress: Förarens mailadress som Tesla-kontot är kopplat ill

  • Tesla-konto lösenord: Förarens lösenord till Tesla-kontot

  • Client id: Ett ID för Teslas app

  • Client secret: En hemlig del av identifieringen (men som är känd)

Kom ihåg att man med förarens användarnamn och lösenord kan göra i princip allt med bilen (inklusive låsa upp, starta etc) så var försiktig med hur du hanterar dessa uppgifter.


Client id och Client secret kan hittas online. De aktuella uppgifterna finns här och om de slutar gälla kan man följa länken här för att hitta nya.


Authorization token hämtas sedan genom att göra ett POST-anrop till följande adress: https://owner-api.teslamotors.com/oauth/token


Som svar kommer vi att få en token som vi sparar ner för att sedan använda i kommande anrop.


Vi börjar med att sätta upp våra olika parametrar som variabler för att lättare kunna byta ut värdena vid behov.


Let vBaseURL = 'https://owner-api.teslamotors.com';
Let vDriverEmail = 'morgan.kejerhag@drakeanalytics.se';
Let vDriverPassword = 'XXXXXXXXXX'; // Byt till ditt lösenord!
Let vClientId = '81527cff06843c8634fdc09e8ac0abefb46ac849f38fe1e431c2ef2106796384';
Let vClientSecret = 'c7257eb71a564034f9419ee651c7d0e5f7aa6bfbd18bafb5c5c033b093bb2fa3';
Let vToken = '';
Let vVehicleID = '';

Därefter sätter vi upp en data connection av typen REST med följande inställningar:

  • URL: https://owner-api.teslamotors.com/oauth/token

  • Method: POST

  • Authentication Schema: Anonymous

  • Query parameter "grant_type": password

  • Query parameter "client_id": Det client id som du tidigare tagit ut

  • Query parameter "client_secret": Den client secret som du tidigare tagit ut

  • Query parameter "email": Epostadress till förarens konto

  • Query parameter "password": Lösenord till förarens konto

  • Query header "User-Agent": Mozilla/5.0 (Linux; Android 9.0.0; VS985 4G Build/LRX21Y; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/58.0.3029.83 Mobile Safari/537.36

  • Query header "X-Tesla-User-Agent": TeslaApp/3.4.4-350/fad4a582e/android/9.0.0

  • Name: Tesla POST REST


Om du sedan trycker på knappen Test connection ska du få ett svar om att anslutningen har lyckats.



Lägg till läsningen till scriptet och justera så att parametrarna sätts i scriptet istället för i inställningarna:


LIB CONNECT TO 'Tesla POST REST';

Token:
SQL SELECT 
	"access_token",
	"token_type",
	"expires_in",
	"refresh_token",
	"created_at"
FROM JSON (wrap on) "root"
WITH CONNECTION(
    Url "https://owner-api.teslamotors.com/oauth/token",
    QUERY "grant_type" "password",
    QUERY "client_id" "$(vClientId)",
    QUERY "client_secret" "$(vClientSecret)",
    QUERY "email" "$(vDriverEmail)",
    QUERY "password" "$(vDriverPassword)",
    HTTPHEADER "User-Agent" "Mozilla/5.0 (Linux; Android 9.0.0; VS985 4G Build/LRX21Y; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/58.0.3029.83 Mobile Safari/537.36",
    HTTPHEADER "X-Tesla-User-Agent" "TeslaApp/3.4.4-350/fad4a582e/android/9.0.0"
);

Let vToken = peek('access_token',0);
drop table Token;

TRACE Token: $(vToken);

Kör en omladdning så ska en token hämtas ut och skrivas ut i loggen. Kopiera denna token så att vi kan använda den när vi sätter upp nästa anslutning för att hämta data.


Hämta Vehicle ID

För att läsa ut data behöver man också veta ett id-nummer för den bil man ska ta data från. Ett Tesla-konto kan ha flera bilar kopplat till sig och då behöver man modifiera scriptet för att hämta antingen en specifik bil eller alla.


Sätt upp en andra REST-anslutning och denna gång med typen GET.

  • URL: https://owner-api.teslamotors.com/api/1/vehicles

  • Method: GET

  • Authentication Schema: Anonymous

  • Query header "Authorization": Bearer <Den token du hämtat ut>

  • Query header "User-Agent": Mozilla/5.0 (Linux; Android 9.0.0; VS985 4G Build/LRX21Y; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/58.0.3029.83 Mobile Safari/537.36

  • Query header "X-Tesla-User-Agent": TeslaApp/3.4.4-350/fad4a582e/android/9.0.0

  • Name: Tesla GET REST



Lägg till läsningen till scriptet och justera så att parametrarna sätts i scriptet istället för i inställningarna:


LIB CONNECT TO 'Tesla GET REST';

Vehicle:
LOAD	
	[id],
	[vehicle_id],
	[vin],
	[display_name]
;
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"id",
		"vehicle_id",
		"vin",
		"display_name",
		"option_codes",
		"color",
		"access_type",
		"state",
		"in_service",
		"id_s",
		"calendar_enabled",
		"api_version",
		"backseat_token",
		"backseat_token_updated_at",
		"vehicle_config",
		"__KEY_response",
		"__FK_response"
	FROM "response" PK "__KEY_response" FK "__FK_response")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(
    Url "https://owner-api.teslamotors.com/api/1/vehicles",
    HTTPHEADER "Authorization" "Bearer $(vToken)",
    HTTPHEADER "User-Agent" "Mozilla/5.0 (Linux; Android 9.0.0; VS985 4G Build/LRX21Y; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/58.0.3029.83 Mobile Safari/537.36",
    HTTPHEADER "X-Tesla-User-Agent" "TeslaApp/3.4.4-350/fad4a582e/android/9.0.0"
);

Let vVehicle = peek('vehicle_id',0);
Let vID = peek('id',0);

drop table Vehicle;

TRACE VehicleID: $(vVehicle);
TRACE ID: $(vID);

Kopiera värdet av ID som skrivs ut i loggen. ID kommer att användas till de anrop vi ska göra för att läsa ut data. En del andra API:r behöver VehicleID.


Hämta data

Nu har vi all information vi behöver och i detta steg ska vi hämta ut data för bilen. Vi ska återanvända vår GET-anslutning och skicka in andra parametrar inklusive bilens ID.


Modifiera Tesla GET REST-anslutningen

  • URL: https://owner-api.teslamotors.com/api/1/vehicles/<id>/vehicle_data (byt <id> till ditt id för bilen)


Lägg till utläsningen och modifiera scriptet för att joina ihop tabellerna och döpa om fälten.


LIB CONNECT TO 'Tesla GET REST';

RestConnectorMasterTable:
SQL SELECT 
	"id",
	"user_id",
	"vehicle_id",
	"vin",
	"display_name",
	"option_codes",
	"color",
	"access_type",
	"state",
	"in_service",
	"id_s",
	"calendar_enabled",
	"api_version" AS "api_version_u0",
	"backseat_token",
	"backseat_token_updated_at",
	"__KEY_response",
	(SELECT 
		"@Value",
		"__FK_tokens"
	FROM "tokens" FK "__FK_tokens" ArrayValueAlias "@Value"),
	(SELECT 
		"can_accept_navigation_requests",
		"can_actuate_trunks",
		"car_special_type",
		"car_type",
		"charge_port_type",
		"default_charge_to_max",
		"ece_restrictions",
		"eu_vehicle",
		"exterior_color",
		"has_air_suspension",
		"has_ludicrous_mode",
		"motorized_charge_port",
		"plg",
		"rear_seat_heaters",
		"rear_seat_type",
		"rhd",
		"roof_color",
		"seat_type",
		"spoiler_type",
		"sun_roof_installed",
		"third_row_seats",
		"timestamp",
		"trim_badging",
		"use_range_badging",
		"wheel_type",
		"__FK_vehicle_config"
	FROM "vehicle_config" FK "__FK_vehicle_config"),
	(SELECT 
		"battery_heater_on",
		"battery_level",
		"battery_range",
		"charge_current_request",
		"charge_current_request_max",
		"charge_enable_request",
		"charge_energy_added",
		"charge_limit_soc",
		"charge_limit_soc_max",
		"charge_limit_soc_min",
		"charge_limit_soc_std",
		"charge_miles_added_ideal",
		"charge_miles_added_rated",
		"charge_port_cold_weather_mode",
		"charge_port_door_open",
		"charge_port_latch",
		"charge_rate",
		"charge_to_max_range",
		"charger_actual_current",
		"charger_phases",
		"charger_pilot_current",
		"charger_power",
		"charger_voltage",
		"charging_state",
		"conn_charge_cable",
		"est_battery_range",
		"fast_charger_brand",
		"fast_charger_present",
		"fast_charger_type",
		"ideal_battery_range",
		"managed_charging_active",
		"managed_charging_start_time",
		"managed_charging_user_canceled",
		"max_range_charge_counter",
		"minutes_to_full_charge",
		"not_enough_power_to_heat",
		"scheduled_charging_pending",
		"scheduled_charging_start_time",
		"time_to_full_charge",
		"timestamp" AS "timestamp_u0",
		"trip_charging",
		"usable_battery_level",
		"user_charge_enable_request",
		"__FK_charge_state"
	FROM "charge_state" FK "__FK_charge_state"),
	(SELECT 
		"battery_heater",
		"battery_heater_no_power",
		"climate_keeper_mode",
		"defrost_mode",
		"driver_temp_setting",
		"fan_status",
		"inside_temp",
		"is_auto_conditioning_on",
		"is_climate_on",
		"is_front_defroster_on",
		"is_preconditioning",
		"is_rear_defroster_on",
		"left_temp_direction",
		"max_avail_temp",
		"min_avail_temp",
		"outside_temp",
		"passenger_temp_setting",
		"remote_heater_control_enabled",
		"right_temp_direction",
		"seat_heater_left",
		"seat_heater_rear_center",
		"seat_heater_rear_left",
		"seat_heater_rear_right",
		"seat_heater_right",
		"side_mirror_heaters",
		"steering_wheel_heater",
		"timestamp" AS "timestamp_u1",
		"wiper_blade_heater",
		"__FK_climate_state"
	FROM "climate_state" FK "__FK_climate_state"),
	(SELECT 
		"gps_as_of",
		"heading",
		"latitude",
		"longitude",
		"native_latitude",
		"native_location_supported",
		"native_longitude",
		"native_type",
		"power",
		"shift_state",
		"speed",
		"timestamp" AS "timestamp_u2",
		"__FK_drive_state"
	FROM "drive_state" FK "__FK_drive_state"),
	(SELECT 
		"gui_24_hour_time",
		"gui_charge_rate_units",
		"gui_distance_units",
		"gui_range_display",
		"gui_temperature_units",
		"show_range_units",
		"timestamp" AS "timestamp_u3",
		"__FK_gui_settings"
	FROM "gui_settings" FK "__FK_gui_settings"),
	(SELECT 
		"api_version",
		"autopark_state_v2",
		"calendar_supported",
		"car_version",
		"center_display_state",
		"df",
		"dr",
		"fd_window",
		"fp_window",
		"ft",
		"homelink_device_count",
		"homelink_nearby",
		"is_user_present",
		"locked",
		"notifications_supported",
		"odometer",
		"parsed_calendar_supported",
		"pf",
		"pr",
		"rd_window",
		"remote_start",
		"remote_start_enabled",
		"remote_start_supported",
		"rp_window",
		"rt",
		"sentry_mode",
		"sentry_mode_available",
		"timestamp" AS "timestamp_u4",
		"valet_mode",
		"valet_pin_needed",
		"vehicle_name",
		"__KEY_vehicle_state",
		"__FK_vehicle_state",
		(SELECT 
			"remote_control_enabled",
			"__FK_media_state"
		FROM "media_state" FK "__FK_media_state"),
		(SELECT 
			"download_perc",
			"expected_duration_sec",
			"install_perc",
			"status",
			"version",
			"__FK_software_update"
		FROM "software_update" FK "__FK_software_update"),
		(SELECT 
			"active",
			"current_limit_mph",
			"max_limit_mph",
			"min_limit_mph",
			"pin_code_set",
			"__FK_speed_limit_mode"
		FROM "speed_limit_mode" FK "__FK_speed_limit_mode")
	FROM "vehicle_state" PK "__KEY_vehicle_state" FK "__FK_vehicle_state")
FROM JSON (wrap off) "response" PK "__KEY_response"
WITH CONNECTION(
    Url "https://owner-api.teslamotors.com/api/1/vehicles/$(vID)/vehicle_data",
    HTTPHEADER "Authorization" "Bearer $(vToken)",
    HTTPHEADER "User-Agent" "Mozilla/5.0 (Linux; Android 9.0.0; VS985 4G Build/LRX21Y; wv) AppleWebKit/537.36 (KHTML, like Gecko) Version/4.0 Chrome/58.0.3029.83 Mobile Safari/537.36",
    HTTPHEADER "X-Tesla-User-Agent" "TeslaApp/3.4.4-350/fad4a582e/android/9.0.0"
);

DataTmp:
LOAD distinct	//[@Value],
	[__FK_tokens] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_tokens]);

left join
LOAD	[can_accept_navigation_requests],
	[can_actuate_trunks],
	[car_special_type],
	[car_type],
	[charge_port_type],
	[default_charge_to_max],
	[ece_restrictions],
	[eu_vehicle],
	[exterior_color],
	[has_air_suspension],
	[has_ludicrous_mode],
	[motorized_charge_port],
	[plg],
	[rear_seat_heaters],
	[rear_seat_type],
	[rhd],
	[roof_color],
	[seat_type],
	[spoiler_type],
	[sun_roof_installed],
	[third_row_seats],
	[timestamp],
	[trim_badging],
	[use_range_badging],
	[wheel_type],
	[__FK_vehicle_config] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_vehicle_config]);

left join
LOAD	[battery_heater_on],
	[battery_level],
	[battery_range],
	[charge_current_request],
	[charge_current_request_max],
	[charge_enable_request],
	[charge_energy_added],
	[charge_limit_soc],
	[charge_limit_soc_max],
	[charge_limit_soc_min],
	[charge_limit_soc_std],
	[charge_miles_added_ideal],
	[charge_miles_added_rated],
	[charge_port_cold_weather_mode],
	[charge_port_door_open],
	[charge_port_latch],
	[charge_rate],
	[charge_to_max_range],
	[charger_actual_current],
	[charger_phases],
	[charger_pilot_current],
	[charger_power],
	[charger_voltage],
	[charging_state],
	[conn_charge_cable],
	[est_battery_range],
	[fast_charger_brand],
	[fast_charger_present],
	[fast_charger_type],
	[ideal_battery_range],
	[managed_charging_active],
	[managed_charging_start_time],
	[managed_charging_user_canceled],
	[max_range_charge_counter],
	[minutes_to_full_charge],
	[not_enough_power_to_heat],
	[scheduled_charging_pending],
	[scheduled_charging_start_time],
	[time_to_full_charge],
	[timestamp_u0] AS [timestamp_u0],
	[trip_charging],
	[usable_battery_level],
	[user_charge_enable_request],
	[__FK_charge_state] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_charge_state]);

left join
LOAD	[battery_heater],
	[battery_heater_no_power],
	[climate_keeper_mode],
	[defrost_mode],
	[driver_temp_setting],
	[fan_status],
	[inside_temp],
	[is_auto_conditioning_on],
	[is_climate_on],
	[is_front_defroster_on],
	[is_preconditioning],
	[is_rear_defroster_on],
	[left_temp_direction],
	[max_avail_temp],
	[min_avail_temp],
	[outside_temp],
	[passenger_temp_setting],
	[remote_heater_control_enabled],
	[right_temp_direction],
	[seat_heater_left],
	[seat_heater_rear_center],
	[seat_heater_rear_left],
	[seat_heater_rear_right],
	[seat_heater_right],
	[side_mirror_heaters],
	[steering_wheel_heater],
	[timestamp_u1] AS [timestamp_u1],
	[wiper_blade_heater],
	[__FK_climate_state] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_climate_state]);

left join
LOAD	[gps_as_of],
	[heading],
	[latitude],
	[longitude],
	[native_latitude],
	[native_location_supported],
	[native_longitude],
	[native_type],
	[power],
	[shift_state],
	[speed],
	[timestamp_u2] AS [timestamp_u2],
	[__FK_drive_state] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_drive_state]);

left join
LOAD	[gui_24_hour_time],
	[gui_charge_rate_units],
	[gui_distance_units],
	[gui_range_display],
	[gui_temperature_units],
	[show_range_units],
	[timestamp_u3] AS [timestamp_u3],
	[__FK_gui_settings] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_gui_settings]);

left join
LOAD	[remote_control_enabled],
	[__FK_media_state] AS [__KEY_vehicle_state]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_media_state]);

left join
LOAD	[download_perc],
	[expected_duration_sec],
	[install_perc],
	[status],
	[version],
	[__FK_software_update] AS [__KEY_vehicle_state]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_software_update]);

left join
LOAD	[active],
	[current_limit_mph],
	[max_limit_mph],
	[min_limit_mph],
	[pin_code_set],
	[__FK_speed_limit_mode] AS [__KEY_vehicle_state]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_speed_limit_mode]);

left join
LOAD	[api_version],
	[autopark_state_v2],
	[calendar_supported],
	[car_version],
	[center_display_state],
	[df],
	[dr],
	[fd_window],
	[fp_window],
	[ft],
	[homelink_device_count],
	[homelink_nearby],
	[is_user_present],
	[locked],
	[notifications_supported],
	[odometer],
	[parsed_calendar_supported],
	[pf],
	[pr],
	[rd_window],
	[remote_start],
	[remote_start_enabled],
	[remote_start_supported],
	[rp_window],
	[rt],
	[sentry_mode],
	[sentry_mode_available],
	[timestamp_u4] AS [timestamp_u4],
	[valet_mode],
	[valet_pin_needed],
	[vehicle_name],
	[__KEY_vehicle_state],
	[__FK_vehicle_state] AS [__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_vehicle_state]);

left join
LOAD	[id],
	[user_id],
	[vehicle_id],
	[vin],
	[display_name],
	[option_codes],
	[color],
	[access_type],
	[state],
	[in_service],
	[id_s],
	[calendar_enabled],
	[api_version_u0] AS [api_version_u0],
	[backseat_token],
	[backseat_token_updated_at],
	[__KEY_response]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_response]);

DROP TABLE RestConnectorMasterTable;

Data:
Load
	now() as Timestamp,
    id as ID,
    user_id as UserID,
    vehicle_id as VehicleID,
    vin as VIN,
    display_name as DisplayName,
    option_codes as OptionCodes,
    color as Color,
    access_type as AccessType,
    state as State,
    in_service as InService,
    id_s as IDs,
    calendar_enabled as CalendarEnabled,
    api_version as ApiVersionU0,
    backseat_token as BackseatToken,
    backseat_token_updated_at as BackseatTokenUpdatedAt,
    can_accept_navigation_requests as CanAcceptNavigationRequests,
    can_actuate_trunks as CanActuateTrunks,
    car_special_type as CarSpecialType,
    car_type as CarType,
    charge_port_type as ChargePortType,
    default_charge_to_max as DefaultChargeToMax,
    ece_restrictions as EceRestrictions,
    eu_vehicle as EuVehicle,
    exterior_color as ExteriorColor,
    has_air_suspension as HasAirSuspension,
    has_ludicrous_mode as HasLudicrousMode,
    motorized_charge_port as MotorizedChargePort,
    plg as PLG,
    rear_seat_heaters as RearSeatHeaters,
    rear_seat_type as RearSeatType,
    rhd as RHD,
    roof_color as RoofColor,
    seat_type as SeatType,
    spoiler_type as SpoilerType,
    sun_roof_installed as SunRoofInstalled,
    third_row_seats as ThirdRowSeats,
    timestamp as Timestamp_u,
    trim_badging as TrimBadging,
    use_range_badging as UseRangeBadging,
    wheel_type as WheelType,
    battery_heater_on as BatteryHeaterOn,
    battery_level as BatteryLevel,
    battery_range as BatteryRange,
    charge_current_request as ChargeCurrentRequest,
    charge_current_request_max as ChargeCurrentRequestMax,
    charge_enable_request as ChargeEnableRequest,
    charge_energy_added as ChargeEnergyAdded,
    charge_limit_soc as ChargeLimitSoc,
    charge_limit_soc_max as ChargeLimitSocMax,
    charge_limit_soc_min as ChargeLimitSocMin,
    charge_limit_soc_std as ChargeLimitSocStd,
    charge_miles_added_ideal as ChargeMilesAddedIdeal,
    charge_miles_added_rated as ChargeMilesAddedRated,
    charge_port_cold_weather_mode as ChargePortColdWeatherMode,
    charge_port_door_open as ChargePortDoorOpen,
    charge_port_latch as ChargePortLatch,
    charge_rate as ChargeRate,
    charge_to_max_range as ChargeToMaxRange,
    charger_actual_current as ChargerActualCurrent,
    charger_phases as ChargerPhases,
    charger_pilot_current as ChargerPilotCurrent,
    charger_power as ChargerPower,
    charger_voltage as ChargerVoltage,
    charging_state as ChargingState,
    conn_charge_cable as ConnChargeCable,
    est_battery_range as EstBatteryRange,
    fast_charger_brand as FastChargerBrand,
    fast_charger_present as FastChargerPresent,
    fast_charger_type as FastChargerType,
    ideal_battery_range as IdealBatteryRange,
    managed_charging_active as ManagedChargingActive,
    managed_charging_start_time as ManagedChargingStartTime,
    managed_charging_user_canceled as ManagedChargingUserCanceled,
    max_range_charge_counter as MaxRangeChargeCounter,
    minutes_to_full_charge as MinutesToFullCharge,
    not_enough_power_to_heat as NotEnoughPowerToHeat,
    scheduled_charging_pending as ScheduledChargingPending,
    scheduled_charging_start_time as ScheduledChargingStartTime,
    time_to_full_charge as TimeToFullCharge,
    timestamp as TimestampU0,
    trip_charging as TripCharging,
    usable_battery_level as UsableBatteryLevel,
    user_charge_enable_request as UserChargeEnableRequest,
    battery_heater as BatteryHeater,
    battery_heater_no_power as BatteryHeaterNoPower,
    climate_keeper_mode as ClimateKeeperMode,
    defrost_mode as DefrostMode,
    driver_temp_setting as DriverTempSetting,
    fan_status as FanStatus,
    inside_temp as InsideTemp,
    is_auto_conditioning_on as IsAutoConditioningOn,
    is_climate_on as IsClimateOn,
    is_front_defroster_on as IsFrontDefrosterOn,
    is_preconditioning as IsPreconditioning,
    is_rear_defroster_on as IsRearDefrosterOn,
    left_temp_direction as LeftTempDirection,
    max_avail_temp as MaxAvailTemp,
    min_avail_temp as MinAvailTemp,
    outside_temp as OutsideTemp,
    passenger_temp_setting as PassengerTempSetting,
    remote_heater_control_enabled as RemoteHeaterControlEnabled,
    right_temp_direction as RightTempDirection,
    seat_heater_left as SeatHeaterLeft,
    seat_heater_rear_center as SeatHeaterRearCenter,
    seat_heater_rear_left as SeatHeaterRearLeft,
    seat_heater_rear_right as SeatHeaterRearRight,
    seat_heater_right as SeatHeaterRight,
    side_mirror_heaters as SideMirrorHeaters,
    steering_wheel_heater as SteeringWheelHeater,
    timestamp as TimestampU1,
    wiper_blade_heater as WiperBladeHeater,
    gps_as_of as GpsAsOf,
    heading as Heading,
    latitude as Latitude,
    longitude as Longitude,
    native_latitude as NativeLatitude,
    native_location_supported as NativeLocationSupported,
    native_longitude as NativeLongitude,
    native_type as NativeType,
    power as Power,
    shift_state as ShiftState,
    speed as Speed,
    timestamp as TimestampU2,
    gui_24_hour_time as Gui24HourTime,
    gui_charge_rate_units as GuiChargeRateUnits,
    gui_distance_units as GuiDistanceUnits,
    gui_range_display as GuiRangeDisplay,
    gui_temperature_units as GuiTemperatureUnits,
    show_range_units as ShowRangeUnits,
    timestamp as TimestampU3,
    api_version as ApiVersion,
    autopark_state_v2 as AutoparkStateV2,
    calendar_supported as CalendarSupported,
    car_version as CarVersion,
    center_display_state as CenterDisplayState,
    df as DF,
    dr as DR,
    fd_window as FdWindow,
    fp_window as FpWindow,
    ft as FT,
    homelink_device_count as HomelinkDeviceCount,
    homelink_nearby as HomelinkNearby,
    is_user_present as IsUserPresent,
    locked as Locked,
    notifications_supported as NotificationsSupported,
    odometer as Odometer,
    parsed_calendar_supported as ParsedCalendarSupported,
    pf as PF,
    pr as PR,
    rd_window as RdWindow,
    remote_start as RemoteStart,
    remote_start_enabled as RemoteStartEnabled,
    remote_start_supported as RemoteStartSupported,
    rp_window as RpWindow,
    rt as RT,
    sentry_mode as SentryMode,
    sentry_mode_available as SentryModeAvailable,
    timestamp as TimestampU4,
    valet_mode as ValetMode,
    valet_pin_needed as ValetPinNeeded,
    vehicle_name as VehicleName,
    download_perc as DownloadPerc,
    expected_duration_sec as ExpectedDurationSec,
    install_perc as InstallPerc,
    status as Status,
    version as Version,
    active as Active,
    current_limit_mph as CurrentLimitMph,
    max_limit_mph as MaxLimitMph,
    min_limit_mph as MinLimitMph,
    pin_code_set as PinCodeSet
resident DataTmp;
drop table DataTmp;

Spara data till QVD

För att få en historik av data ska vi spara ner informationen till en QVD. Därefter kan vi läsa in varje undansparad QVD och sammanställa i applikationen.


Vi börjar med att skapa en variabel med värden för dagens datum och tid. Dessa ska läggas till i namnet på QVD-filen så att vi inte skriver över filer.

Dessutom lägger vi till värdet 1 i ett fält FlagLatest för att lätt kunna plocka ut det senaste datat efter vi slagit samman alla filer.


Appen ska senare schemaläggas en gång i timmen och på så sätt får vi 24 datapunkter per dag.


Let vNow = text(timestamp(now(),'YYYYMMDD_hhmm'));

store Data into [lib://:DataFiles/Tesla_$(vNow).qvd] (qvd);

left join (Data) Load 1 as FlagLatest AutoGenerate(1);

concatenate (Data)
LOAD * FROM [lib://DataFiles/Tesla_*.qvd] (qvd) where not exists(Timestamp);

Transformera data

I detta steg ska vi transformera det inlästa datat så att det blir lättare att hantera i slutapplikationen. När vi läser data läser vi det i tidsordning. Det gör att för en given transaktion är transaktionen innan den från timmen innan. Detta kan vi använda för att rita ut hur bilen rört sig på en karta. Föregående rads bilkoordinater till denna rads bilkoordinater.


Vi börjar med att sätta upp två par av variabler för koordinater för hem och arbete - de platser där vi oftast befinner oss. Har du fler platser du ofta besöker och vill hantera kan du lägga till fler. Vi använder variablerna i en klassificering av Home, Work & Other genom att räkna ut om bilen befunnit sig inom en viss radie från respektive koordinatuppsättning. Värdet "0.5" i formlen säger att bilen ska ha befunnit sig inom 500m från hem-/arbetskoordinaterna för att sägas tillhöra den platsen.


Vi kan också ta denna rads trippmätarinställning minus föregående rads värde för att se hur långt bilen förflyttat sig senaste timmen.


Let HOME_LATITUDE = 58.3577757;
Let HOME_LONGITUDE = 15.3033063;

Let WORK_LATITUDE = 58.3941658;
Let WORK_LONGITUDE = 15.5570683;

Data2:
Load
	*,
    peek(Latitude) as PeekLatitude,
    peek(Longitude) as PeekLongitude,
    RowNo() as RowNo,
    if(acos(sin(Latitude * 0.0175) * sin($(HOME_LATITUDE) * 0.0175) + cos(Latitude * 0.0175) * cos($(HOME_LATITUDE) * 0.0175) * cos(($(HOME_LONGITUDE) * 0.0175) - (Longitude * 0.0175))) * 6371 <= 0.5,
    	'Home',
        if(acos(sin(Latitude * 0.0175) * sin($(WORK_LATITUDE) * 0.0175) + cos(Latitude * 0.0175) * cos($(WORK_LATITUDE) * 0.0175) * cos(($(WORK_LONGITUDE) * 0.0175) - (Longitude * 0.0175))) * 6371 <= 0.5, 
        	'Work',
        	'Other'
        )
   	) as Location,   
    
    date(floor(Timestamp),'YYYY-MM-DD') as %Date,
    if(Timestamp > now()-3,1,0) as FlagLast72Hours,
    if(hour(Timestamp)<>hour(peek(Timestamp)),1,0) as FlagFirstInHour,
    Odometer-peek(Odometer) as DrivenDistance
resident Data order by Timestamp asc;
drop table Data;

Kalender

Skapa sedan en kalender på ditt favoritsätt. Jag använder ofta detta skript:


TmpDate:
Load
	num(min(fieldvalue('%Date',recno()))) as MinDate,
	num(max(fieldvalue('%Date',recno()))) as MaxDate
AutoGenerate FieldValueCount('%Date');

Let vMinDate = peek('MinDate',0);
Let vMaxDate = peek('MaxDate',0);
drop table TmpDate;

Calendar:
Load
	day(Date) as Day,
     month(Date) as Month,
	'Q' & ceil(month(Date)/3) as Quarter,
     year(Date) as Year,
     year(Date) & num(month(Date),'00') as YearMonth,
     weekyear(Date) & num(week(Date),'00') as YearWeek,
     if(Date > today()-7,1,0) as FlagLast7Days,
     Date,
     Date as %Date;
Load
	date($(vMinDate)+recno()-1) as Date
AutoGenerate($(vMaxDate)-$(vMinDate)+1);

Layout

Nu kan vi skapa de objekt och beräkningar som vi tycker är intressanta. Här följer några vanliga uttryck. Då distanser är lagrade som miles måste vi omvandla dessa till kilometer genom att multiplicera med 1.60934.


Körd distans (km):

Sum(DrivenDistance)*1.60934


Laddning (%):

avg(BatteryLevel)/100


Utomhustemperatur:

avg(OutsideTemp)


Inomhustemperatur:

avg(InsideTemp)


Uppskattad möjlig körsträcka (km):

sum(EstBatteryRange)*1.60934


Antal kWh vid senaste laddning:

sum(ChargeEnergyAdded)


Skriven av: Morgan Kejerhag

Morgan Kejerhag har arbetat med Qlik-plattformen sedan 2005 och är en av Sveriges mest erfarna konsulter. Under åren har Morgan arbetat med flertalet multinationella bolag där han lett arbetet i att bygga upp stora Qlik-miljöer såväl som små kunder. LinkedIn Kontaktuppgifter