Het is mogelijk om gegevens uit ClockWise te visualiseren in Qlik. Dit kan gedaan worden met behulp van de "Data Load editor" (Editor voor het laden van gegevens). In deze handleiding worden de stappen uitgelegd hoe op een veilige manier automatisch alle gegevens uit ClockWise in Qlik kunnen worden geladen
Om met Qlik te kunnen werken, moet een API client aangemaakt worden. Dit kan in Configutie → Instellingen → OAuth/API clients. Klik op 'OAuth client toevoegen', en geef een naam aan de client (bijv. Qlik), en druk op Opslaan. Let op: We hebben de Client ID die hier staat later nodig, dus hou de pagina open en/of noteer deze ergens.
Om de verbinding te kunnen maken, hebben we twee(!) REST connectoren nodig in Qlik: eentje om de data op te halen, en de andere om de authenticatie te regelen op een veilige manier.
Maak een REST connector aan met de volgende waardes:
Hier is nog een extra stap voor nodig: We moeten een authorizatiecode opvragen om de eerste keer de data op te kunnen halen.
De eerste keer wordt deze ingewisseld voor een "accesstoken" en een "refreshtoken". De eerste hiervan is een soort 'inlogbewijs', de tweede kan de eerste verversen zodat er maar een keer ingelogd hoeft te worden, en niet elke keer als Qlik de waardes wil verversen.
Let op! De authorizatiecode is één uur geldig, dus tussen het ophalen van de authenticatie code en het voor de eerste keer ophalen van de data mag maximaal één uur zitten. Anders moet de komende stap opnieuw uitgevoerd worden.
Ga in de browser naar uwaccountnaam.clockwise.info/api/v2/auth?response_type=code&redirect_uri=https%3A%2F%2Fexample.com&client_id=Client ID, met uwaccountnaam de naam van uw ClockWise account en Client ID de client id die we hadden opgeslagen bij de API client.
Als alles goed is ingevuld wordt u naar de inlogpagina geleid. Log hier in met uw inloggegevens (of met de inloggegevens van een speciale data gebruikers). Daarna wordt u doorgeleid naar "example.com", en staat er in de URL-balk "example.com/?code=authorizatiecode"
Sla deze authorizatiecode op.
Ga weer terug naar Qlik. Maak een REST connector met de volgende waardes:
{
"code": "*authorizatiecode*",
"grant_type": "authorization_code",
"client_id": "*Client ID*"
}
Open nu een Qlik applicatie, en ga naar een werkblad. Open de Data Load Editor (Editor voor het laden gegevens).
Maak een nieuw script als de applicatie al bestond. Kopieer en plak onderstaande in de editor:
(onder de standaard variabelen die Qlik zelf al neerzet)
// Macro for the parameters
SET GETFIELDS='PurgeChar($1Fields, ''"'')';
/*
ClockWise account parameters. Deze zijn nodig om de verbinding goed op te zetten:
RESTName: De naam van de REST verbinding naar uwaccountnaam.clockwise.info/api/v2
RESTToken: De naam van de REST verbinding naar uwaccountnaam.clockwise.info/api/v2/token
accountName: De naam van het ClockWise account (vaak gebaseerd op de naam van het bedrijf)
client_id: De Client ID van de API client. Een beheerder kan deze aanmaken bij Configuratie->Instellingen->Oauth/API clients.
*/
SET RESTName=*Rest_data*;
SET RESTToken=*Rest_token*;
SET accountName=*uwaccountnaam*;
SET client_id=*Client ID*;
/*
Parameters voor het ophalen van de data
Deze worden meegegeven alsof ze in de url staan (veld=waarde&veld2=waarde2&...)
includeRates: 1 of 0. Geeft aan of de tarieven wel (1) of niet (0) moeten worden opgehaald.
employeeParameters, resourceParameters, projectParameters en hourParameters: De parameters voor de vier tabellen:
Medewerkers, Koppelingen, Projecten en Uren. Deze zijn al gevuld met de veldnamen en de grootte van de batches. PAS DEZE ALLEEN AAN ALS U WEET WAT U DOET!!
Extra veldnamen kunnen erbij worden gevoegd door vlak voor de &limit=10000 een komma gescheiden lijst mee te geven, beginnend met een komma (bijv ...&$(GETFIELDS(employee))&',my_employee_field,my_second_employee_field,!employee_name&limit=10000'
Let op! De groepnamen worden niet goed ondersteunt! Geef de losse veldnamen zodat deze ook goed in de tabel kunnen worden ingeladen! (Anders komen er lege velden zoals "defaultfields" of iets dergelijks in de tabel terecht...)
Een uitroepteken zorgt ervoor dat het veld niet opgehaald wordt.
Daarnaast kunnen hier extra parameters bij worden gevoegd, zoals de {parameter}_split, of allprojects, etc.
Zie voor een volledig overzicht https://uwaccountnaam.clockwise.info/api/v2/doc#hours-in-flat-format, met 'uwaccountnaam' uw ClockWise account naam.
*/
SET includeRates=0;
SET employeeParameters='fields='&$(GETFIELDS(employee))&'&limit=10000';
SET resourceParameters='fields='&$(GETFIELDS(resource))&'&limit=10000';
SET projectParameters='fields='&$(GETFIELDS(project))&'&limit=10000';
SET hourParameters='fields='&$(GETFIELDS(hour))&'&limit=10000';
// Actual begin of script
// If we still have a refreshToken and accessToken, we preload them
if not isNull(refreshToken) and not isNull(accessToken) then
"Tokens":
LOAD token, refresh inline [
token refresh
$(accessToken) $(refreshToken)
] (delimiter is spaces);
end if
// This subroutine creates a table with the given tablename, based on the fields given in query,
// loading data from the endpoint given by url, optionally renaming the fieldnames using a mapping table,
// and optionally renaming the id (to a duplicate name in another table) using renameID.
SUB getRecords(tablename, query, url, renameTable, renameID)
// apiname -> actual clockwise url
let url='https://$(accountName).clockwise.info/api/v2$(url)';
LIB CONNECT TO $(RESTName);
SET ErrorMode=0;
// A retry-on-error policy is used to load the data. There are 3 attempts. If it fails, a new attempt is made using a new access token, and
// the attepmt is made again. After 3 attempts, we stop to avoid endless loops
FOR i=1 to 3
DO
if not isNull(accessToken) then
// If we have an access token, we try to retrieve the given fields from the given url
result:
REPLACE SELECT "Link",
"__KEY_response_header",
(SELECT $(query),
"__FK_root"
FROM (wrap on) "root" FK "__FK_root")
FROM JSON "_response_header" PK "__KEY_response_header"
WITH CONNECTION(URL "$(url)", HTTPHEADER "Authorization" "Bearer $(accessToken)");
if ScriptError = 12 then // Probably unauthorized error. Invalidate access token.
let accessToken=null();
drop table result;
else
// No error, we look if there are more pages to load (pagination)
LET Link = peek('Link', 0, 'result');
// We copy the data from the temporary table "result" to the actual table $(tablename), to get rid of the extra keys and the header.
// If renameID is given, we rename the id over here to the given name
if not isNull(renameID) then
"$(tablename)":
LOAD id as $(renameID), *;
LOAD $(query) RESIDENT result;
DROP FIELD id FROM $(tablename);
else
"$(tablename)":
LOAD $(query) RESIDENT result;
end if
// We parse the Link header to see if there is a "next" page
Link:
LOAD TextBetween(link, '<', '>') as new_url WHERE rel LIKE '*next*';
LOAD SubField(link1, ';', 1) AS link, SubField(link1, ';', 2) AS rel;
LOAD SubField('$(Link)', ',') AS link1 AUTOGENERATE 1;
let url = peek('new_url', 0, 'Link 2 $(tablename)');
drop table Link;
end if
end if
// If we got another url from the Link header, we loop again.
// If there was no accessToken, we stop the loop execution to retrieve a new access token
LOOP UNTIL isNull(url) or isNull(accessToken);
if not isNull(accessToken) then
if not isNull(renameTable) then
// We rename the fields using the mapping table. In order to avoid concatenation problems, we do this outside the pagination loop
RENAME FIELDS USING "$(renameTable)";
end if
drop table result;
end if
if isNull(accessToken) then // accessToken did not exist yet or was invalid
LIB CONNECT TO $(RESTToken);
SET ScriptError=2; // If retrieving a new token is not successful, stop execution. A serious error occured.
if not isNull(refreshToken) then // Retrieve with the refreshToken
set refreshString =[{""grant_type"": ""refresh_token"", ""client_id"": ""$(client_id)"", ""refresh_token"": ""$(refreshToken)""}];
Tokens:
REPLACE LOAD "access_token" as "token", '$(refreshToken)' as "refresh";
SELECT "access_token"
FROM JSON(wrap on) "root"
WITH CONNECTION(BODY "$(refreshString)");
LET accessToken = peek('token', 0, 'Tokens');
else // Use the authorization code which was filled in in the REST connector
Tokens:
REPLACE SELECT "access_token" as "token", "refresh_token" as "refresh"
FROM JSON(wrap on) "root";
LET refreshToken = peek('refresh', 0, 'Tokens');
LET accessToken = peek('token', 0, 'Tokens');
end if;
// Connect with the REST connector again
LIB CONNECT TO $(RESTName);
// and reset the scripterror value again (so that the script won't halt when the new accessToken is somehow invalid)
SET ScriptError=0;
else
// We are done loading the data. Stop the retry loop and exit
exit for;
end if;
// If we are here, we retrieved a new access token. We should retry getting the data, therefore loop again.
NEXT i;
// Reset the error variable. Ignoring the error was just for the SELECT step to catch the unauthorized errors
SET ErrorMode=1;
END SUB
// This subroutine parses the given GET parameters, to modify which fields are loaded.
// As a part of the parameters are generated using the fields, we need to compare the new fields with the fields we already have
SUB getFieldsFromParameters(parameters, fields)
// Set the parameters in a table, so that they can be processed below
ParameterTable:
LOAD SubField(parameter, '=', 1) AS parameterName, SubField(parameter, '=', 2) AS parameterValue;
LOAD SubField('$(parameters)', '&') AS parameter AUTOGENERATE 1;
// Process 1: Get all the extra fields given in the parameters which were not already retrieved via the GETFIELD macro.
// When an exclamation mark is given, we need to exclude the field.
// FieldNames contains the fields from the 'fields' variable
FieldNames:
LOAD TextBetween(fieldNames, '"', '"') as fieldNames;
LOAD SubField('$(fields)', ',') AS fieldNames AUTOGENERATE 1;
// ExtraFields contains the fields from the fields query pameter in the 'parameters' variable which do not exist in the fields variable
ExtraFields:
LOAD extraFieldNames WHERE NOT Exists(fieldNames, extraFieldNames); // Check which fields do not already exist in the FieldNames table
NOCONCATENATE LOAD SubField(parameterValue, ',') as extraFieldNames RESIDENT ParameterTable WHERE parameterName = 'fields';
// NewFields concatenates the extra fields to the already exsting fields, unless they begin with '!'. In that case, they are excluded instead.
NewFields:
NOCONCATENATE LOAD fieldNames RESIDENT FieldNames WHERE NOT EXISTS(extraFieldNames, '!'&fieldNames);
CONCATENATE (NewFields) LOAD extraFieldNames as fieldNames RESIDENT ExtraFields WHERE NOT Left(extraFieldNames, 1) = '!';
// We now need te convert the "list" of fieldnames into a string again
FieldString:
Load '"' & Concat(fieldNames, '","') & '"' as string RESIDENT NewFields;
fields = peek('string');
// Process 2: Process the split parameters to replace the '{pathlist}_path' field into '{pathlist}_\d+' fields
// We do this in a triple load statement, where we find the number of columns we need to generate, we process the parameter to get the {pathlist}_path out of
// {pathlist}_split, and we create a mapping table out of the _path fields and new generated _\d+ fields
Parameters:
MAPPING LOAD '"' & parameterBase & 'path"' AS oldFieldNames, '"'&Concat(newFieldName, '","')&'"' AS fieldNames GROUP BY parameterBase;
LOAD parameterBase, parameterBase & IterNo() AS newFieldName WHILE IterNo() <= parameterCount;
LOAD parameterName, Left(parameterName, Index(parameterName, '_split', -1)) AS parameterBase,
IF(parameterValue='*', 9, SubStringCount(parameterValue, ',')+2) AS parameterCount
RESIDENT ParameterTable WHERE parameterName LIKE '*_split';
// We do a search/replace on the fields using the just generated table
fields = MapSubString('Parameters', fields);
// clean up
DROP TABLES ParameterTable, FieldNames, ExtraFields, NewFields, FieldString;
END SUB
// The mapping tables, to rename fields which are duplicate in the different tables, but are not meant as keys between the tables
EmployeesMap:
MAPPING LOAD old_employee, new_employee INLINE [
old_employee new_employee
hourly_sales_rate employee_hourly_sales_rate
hourly_sales_rate_currency employee_hourly_sales_rate_currency
hourly_sales_rate_id employee_hourly_sales_rate_id
hourly_sales_rate_name employee_hourly_sales_rate_name
hourly_sales_rate_start employee_hourly_sales_rate_start
km_sales_rate employee_km_sales_rate
km_sales_rate_currency employee_km_sales_rate_currency
km_sales_rate_id employee_km_sales_rate_id
km_sales_rate_name employee_km_sales_rate_name
km_sales_rate_start employee_km_sales_rate_start
unit_sales_rate employee_unit_sales_rate
unit_sales_rate_currency employee_unit_sales_rate_currency
unit_sales_rate_id employee_unit_sales_rate_id
unit_sales_rate_name employee_unit_sales_rate_name
unit_sales_rate_start employee_unit_sales_rate_start
hourly_purchase_rate employee_hourly_purchase_rate
hourly_purchase_rate_currency employee_hourly_purchase_rate_currency
hourly_purchase_rate_id employee_hourly_purchase_rate_id
hourly_purchase_rate_name employee_hourly_purchase_rate_name
hourly_purchase_rate_start employee_hourly_purchase_rate_start
hourly_wage employee_hourly_wage
hourly_wage_currency employee_hourly_wage_currency
hourly_wage_id employee_hourly_wage_id
hourly_wage_name employee_hourly_wage_name
hourly_wage_start employee_hourly_wage_start
km_purchase_rate employee_km_purchase_rate
km_purchase_rate_currency employee_km_purchase_rate_currency
km_purchase_rate_id employee_km_purchase_rate_id
km_purchase_rate_name employee_km_purchase_rate_name
km_purchase_rate_start employee_km_purchase_rate_start
unit_purchase_rate employee_unit_purchase_rate
unit_purchase_rate_currency employee_unit_purchase_rate_currency
unit_purchase_rate_id employee_unit_purchase_rate_id
unit_purchase_rate_name employee_unit_purchase_rate_name
unit_purchase_rate_start employee_unit_purchase_rate_start
] (delimiter is spaces);
ResourcesMap:
MAPPING LOAD old_resources, new_resources INLINE [
old_resources new_resources
resource_parent_id project_id
hourly_sales_rate resource_hourly_sales_rate
hourly_sales_rate_currency resource_hourly_sales_rate_currency
hourly_sales_rate_id resource_hourly_sales_rate_id
hourly_sales_rate_name resource_hourly_sales_rate_name
hourly_sales_rate_start resource_hourly_sales_rate_start
km_sales_rate resource_km_sales_rate
km_sales_rate_currency resource_km_sales_rate_currency
km_sales_rate_id resource_km_sales_rate_id
km_sales_rate_name resource_km_sales_rate_name
km_sales_rate_start resource_km_sales_rate_start
unit_sales_rate resource_unit_sales_rate
unit_sales_rate_currency resource_unit_sales_rate_currency
unit_sales_rate_id resource_unit_sales_rate_id
unit_sales_rate_name resource_unit_sales_rate_name
unit_sales_rate_start resource_unit_sales_rate_start
hourly_purchase_rate resource_hourly_purchase_rate
hourly_purchase_rate_currency resource_hourly_purchase_rate_currency
hourly_purchase_rate_id resource_hourly_purchase_rate_id
hourly_purchase_rate_name resource_hourly_purchase_rate_name
hourly_purchase_rate_start resource_hourly_purchase_rate_start
hourly_wage resource_hourly_wage
hourly_wage_currency resource_hourly_wage_currency
hourly_wage_id resource_hourly_wage_id
hourly_wage_name resource_hourly_wage_name
hourly_wage_start resource_hourly_wage_start
km_purchase_rate resource_km_purchase_rate
km_purchase_rate_currency resource_km_purchase_rate_currency
km_purchase_rate_id resource_km_purchase_rate_id
km_purchase_rate_name resource_km_purchase_rate_name
km_purchase_rate_start resource_km_purchase_rate_start
unit_purchase_rate resource_unit_purchase_rate
unit_purchase_rate_currency resource_unit_purchase_rate_currency
unit_purchase_rate_id resource_unit_purchase_rate_id
unit_purchase_rate_name resource_unit_purchase_rate_name
unit_purchase_rate_start resource_unit_purchase_rate_start
absent resource_absent
cost_center_id resource_cost_center_id
cost_center_name resource_cost_center_name
cost_center_number resource_cost_center_number
cost_unit_id resource_cost_unit_id
cost_unit_name resource_cost_unit_name
cost_unit_number resource_cost_unit_number
end_date resource_end_date
hours_budgetted resource_hours_budgetted
ledger_number resource_ledger_number
ledger_id resource_ledger_id
ledger_name resource_legdger_name
start_date resource_start_date
] (delimiter is spaces);
ProjectsMap:
MAPPING LOAD old_projects, new_projects INLINE [
old_projects new_projects
hourly_sales_rate project_hourly_sales_rate
hourly_sales_rate_currency project_hourly_sales_rate_currency
hourly_sales_rate_id project_hourly_sales_rate_id
hourly_sales_rate_name project_hourly_sales_rate_name
hourly_sales_rate_start project_hourly_sales_rate_start
km_sales_rate project_km_sales_rate
km_sales_rate_currency project_km_sales_rate_currency
km_sales_rate_id project_km_sales_rate_id
km_sales_rate_name project_km_sales_rate_name
km_sales_rate_start project_km_sales_rate_start
unit_sales_rate project_unit_sales_rate
unit_sales_rate_currency project_unit_sales_rate_currency
unit_sales_rate_id project_unit_sales_rate_id
unit_sales_rate_name project_unit_sales_rate_name
unit_sales_rate_start project_unit_sales_rate_start
hourly_purchase_rate project_hourly_purchase_rate
hourly_purchase_rate_currency project_hourly_purchase_rate_currency
hourly_purchase_rate_id project_hourly_purchase_rate_id
hourly_purchase_rate_name project_hourly_purchase_rate_name
hourly_purchase_rate_start project_hourly_purchase_rate_start
hourly_wage project_hourly_wage
hourly_wage_currency project_hourly_wage_currency
hourly_wage_id project_hourly_wage_id
hourly_wage_name project_hourly_wage_name
hourly_wage_start project_hourly_wage_start
km_purchase_rate project_km_purchase_rate
km_purchase_rate_currency project_km_purchase_rate_currency
km_purchase_rate_id project_km_purchase_rate_id
km_purchase_rate_name project_km_purchase_rate_name
km_purchase_rate_start project_km_purchase_rate_start
unit_purchase_rate project_unit_purchase_rate
unit_purchase_rate_currency project_unit_purchase_rate_currency
unit_purchase_rate_id project_unit_purchase_rate_id
unit_purchase_rate_name project_unit_purchase_rate_name
unit_purchase_rate_start project_unit_purchase_rate_start
] (delimiter is spaces);
// Unfortunately, you cannot load all in one time, so we make a table containing the fields we can get from the API
// The columns with '1' or '0' denote in which endpoint we should load this field. The "rates" column is controlled by the includeRates variable
Fields:
LOAD * INLINE [
fieldname Employees Projects Hours Resources rates
id 1 1 1 1 0
customer_code_path 0 1 0 0 0
customer_id_path 0 1 0 0 0
customer_name_path 0 1 0 0 0
customer_number_path 0 1 0 0 0
date 0 0 1 0 0
day 0 0 1 0 0
department_code_path 1 0 0 0 0
department_id_path 1 0 0 0 0
department_name_path 1 0 0 0 0
employee_id 0 0 0 1 0
employee_name 1 0 0 0 0
hours 0 0 1 0 0
project_code_path 0 1 0 0 0
project_id_path 0 1 0 0 0
project_name_path 0 1 0 0 0
remark 0 0 1 0 0
resource_id 0 0 1 0 0
resource_parent_id 0 0 0 1 0
resource_status 0 0 0 1 0
weeknumber 0 0 1 0 0
absent 0 1 0 1 0
cost_center_id 0 1 0 1 0
cost_center_name 0 1 0 1 0
cost_center_number 0 1 0 1 0
cost_unit_id 0 1 0 1 0
cost_unit_name 0 1 0 1 0
cost_unit_number 0 1 0 1 0
end_date 0 1 0 1 0
hours_budgetted 0 1 0 1 0
internal 0 1 0 0 0
ledger_id 0 1 0 1 0
ledger_name 0 1 0 1 0
ledger_number 0 1 0 1 0
projectmanagers 0 1 0 0 0
project_type_inherited 0 1 0 0 0
reference_number 0 1 0 0 0
start_date 0 1 0 1 0
unbillable 0 1 0 0 0
authorization 1 0 0 0 0
contract_end 0 0 0 0 0
contract_hours_max 1 0 0 0 0
contract_hours_min 1 0 0 0 0
contract_hours_scope 0 0 0 0 0
contract_start 0 0 0 0 0
contract_type 0 0 0 0 0
employee_email 1 0 0 0 0
employee_function 1 0 0 0 0
salary_number 1 0 0 0 0
usergroup_id 1 0 0 0 0
usergroup_name 1 0 0 0 0
hourly_sales_rate 1 1 1 1 1
hourly_sales_rate_currency 1 1 1 1 1
hourly_sales_rate_id 1 1 1 1 1
hourly_sales_rate_name 1 1 1 1 1
hourly_sales_rate_start 1 1 1 1 1
invoice_id 0 0 1 0 1
invoice_number 0 0 1 0 1
km_sales_rate 1 1 1 1 1
km_sales_rate_currency 1 1 1 1 1
km_sales_rate_id 1 1 1 1 1
km_sales_rate_name 1 1 1 1 1
km_sales_rate_start 1 1 1 1 1
unit_sales_rate 1 1 1 1 1
unit_sales_rate_currency 1 1 1 1 1
unit_sales_rate_id 1 1 1 1 1
unit_sales_rate_name 1 1 1 1 1
unit_sales_rate_start 1 1 1 1 1
hourly_purchase_rate 1 1 1 1 1
hourly_purchase_rate_currency 1 1 1 1 1
hourly_purchase_rate_id 1 1 1 1 1
hourly_purchase_rate_name 1 1 1 1 1
hourly_purchase_rate_start 1 1 1 1 1
hourly_wage 1 1 1 1 1
hourly_wage_currency 1 1 1 1 1
hourly_wage_id 1 1 1 1 1
hourly_wage_name 1 1 1 1 1
hourly_wage_start 1 1 1 1 1
km_purchase_rate 1 1 1 1 1
km_purchase_rate_currency 1 1 1 1 1
km_purchase_rate_id 1 1 1 1 1
km_purchase_rate_name 1 1 1 1 1
km_purchase_rate_start 1 1 1 1 1
reverse_invoice_id 0 0 1 0 1
reverse_invoice_number 0 0 1 0 1
unit_purchase_rate 1 1 1 1 1
unit_purchase_rate_currency 1 1 1 1 1
unit_purchase_rate_id 1 1 1 1 1
unit_purchase_rate_name 1 1 1 1 1
unit_purchase_rate_start 1 1 1 1 1
hourstatus 0 0 1 0 0
hourstatus_modified_at 0 0 1 0 0
hourstatus_modified_by 0 0 1 0 0
comute_km 0 0 1 0 0
expenses 0 0 1 0 0
export_ch1_id 0 0 1 0 0
export_ch2_id 0 0 1 0 0
pause 0 0 0 0 0
start 0 0 0 0 0
travel_costs 0 0 1 0 0
travel_km 0 0 1 0 0
units 0 0 1 0 0
] (delimiter is spaces);
// For each of the four tables, we do the following:
// - We load the fieldnames from the Fields table based on the '1's and '0's of the corresponding field (and the includeRates variable)
// - We expand the macro of the parameters (this macro is based on the *Fields variable loaded just before) to load the GET-parameters.
// - We adjust the fields based on the GET parameters (the ones which were not generated by the macro)
// - We retrieve the records based on the fields we just calculated.
EmployeeFields:
NOCONCATENATE LOAD '"'&ConCat(fieldname, '","')&'"' as fieldnames RESIDENT Fields WHERE Employees=1 AND ($(includeRates)=1 OR rates=0) GROUP BY Employees;
let employeeFields = peek('fieldnames', 0, 'EmployeeFields');
let parameters = $(employeeParameters);
call getFieldsFromParameters(parameters, employeeFields);
call getRecords('Employees', employeeFields, '/report/flat/employees?'& parameters, 'EmployeesMap', 'employee_id');
ResourceFields:
NOCONCATENATE LOAD '"'&ConCat(fieldname, '","')&'"' as fieldnames RESIDENT Fields WHERE Resources=1 AND ($(includeRates)=1 OR rates=0) GROUP BY Resources;
let resourceFields = peek('fieldnames', 0, 'ResourceFields');
let parameters = $(resourceParameters);
call getFieldsFromParameters(parameters, resourceFields);
call getRecords('Resources', resourceFields, '/report/flat/resources?'& parameters, 'ResourcesMap', 'resource_id');
ProjectFields:
NOCONCATENATE LOAD '"'&ConCat(fieldname, '","')&'"' as fieldnames RESIDENT Fields WHERE Projects=1 AND ($(includeRates)=1 OR rates=0) GROUP BY Projects;
let projectFields = peek('fieldnames', 0, 'ProjectFields');
let parameters = $(projectParameters);
call getFieldsFromParameters(parameters, projectFields);
call getRecords('Projects', projectFields, '/report/flat/projects?'& parameters, 'ProjectsMap', 'project_id');
HourFields:
NOCONCATENATE LOAD '"'&Concat(fieldname, '","')&'"' as fieldnames RESIDENT Fields WHERE Hours=1 AND ($(includeRates)=1 OR rates=0) GROUP BY Hours;
let hourFields = peek('fieldnames', 0, 'HourFields');
let parameters = $(hourParameters);
call getFieldsFromParameters(parameters, hourFields);
call getRecords('Hours', hourFields, '/report/flat/hours?'& parameters);
DROP TABLES Fields, EmployeeFields, ProjectFields, ResourceFields, HourFields, Tokens;
// Auto-generate sub-fields based on the date fields, to allow analysis on years, months etc.
"autoCalendar":
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [resource_start_date], [resource_end_date], [date], [hourstatus_modified_at], [start_date], [end_date] USING [autoCalendar] ;
Bovenaan staan een aantal variabelen die aagepast moeten worden om de applicatie te kunnen laten werken. Deze staan in de code al uitgelegd, maar hier beschrijven we ze nog eens kort:
field='&$(GETRATES(employee))&',!employee_name,hourly_sales_rate_id&limit=10000. Ook kunnen extra parameters worden toegevoegd aan deze variabele (bijvoorbeeld department_name_split of iets dergelijks)...Door nu op "Gegevens laden" te klikken, worden de gegevens van ClockWise in Qlik geladen.
Let op! Mogelijk wordt er in het begin een foutmelding geprint met (Connector error: HTTP protocol error 401 (Unauthorized):. Dit geeft aan dat Qlik nieuwe inloggegevens op gaat vragen. Zolang dit de enige foutmelding is die geprint wordt (en ook maar een keer), kan u deze foutmelding negeren.
De foutmelding "HTTP protocol error 400" zou kunnen aangeven dat een van de ...Parameters variabelen een fout heeft (een veld die ongeldig is, of een syntaxis fout, of iets dergelijks). Kijk naar de foutmelding voor de details.
Een andere reden voor de "HTTP protocol error 400" kan zijn dat er mogelijk te veel velden aan staan. Dit is te herkennen aan de foutmelding Illegal field identifier:
Als er geen grootboeken, kostenplaatsen of -dragers in ClockWise ingesteld kunnen worden, dan zijn de velden cost_center_..., cost_unit_... en ledger_... overbodig, en geven foutmeldingen (vervang de '1' in '0' in deze rijen).
Daarnaast, als includeRates aan staat, en eenheden niet gefactureerd kunnen worden, zijn alle velden startend met unit_ (dus unit_sales_... en unit_purchase_...) overbodig, en moeten ook uit worden gezet door '1'en in '0'en te vervangen. Ook kunnen kilometerinkooptarieven niet altijd beschikbaar zijn, dus km_purchase_... velden zouden in zo'n geval ook uitgezet moeten worden.
info@clockwise.info
