Loading a redmine database into qlikview. First draft, let me know if you have improvements. :)

Redmine_Qlikview_0.01

///$tab Redmine
SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 kr;-#.##0,00 kr';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='må;ti;on;to;fr;lö;sö';




ODBC CONNECT TO redmine;

MapStatusIDtoStatus:
	Mapping
	LOAD 
	 id AS StatusID, 
	 name AS Status;
	SQL SELECT id, name FROM redmine.`issue_statuses`;


MapAuthorIDtoAuthor:
Mapping
	LOAD 
	 id AS AuthorID,
	 login AS Author;
	SQL SELECT id, login FROM redmine.users;

MapAssigneeIDtoAssignee:
	Mapping 
	Load 
	  id AS AssigneeID, 
	  login AS Assignee;
	SELECT id, login FROM redmine.users;


MapProjectIDtoProject:
	Mapping
	LOAD 
	 id AS [ProjectID], 
	 name AS [Project Name];
	SQL SELECT id, name FROM redmine.projects;


MapTargetVersionIDtoTargetVersion:
	Mapping
	LOAD 
	 id AS [TargetVersionID], 
	 name AS [Target Version];
	SQL SELECT id, name, `project_id`, status FROM redmine.versions;

MapBusinessValueToIssue:
	Mapping
	LOAD
		customized_id AS IssueID,
		value AS BusinessValue;
	SQL SELECT 
			cv.customized_id, 
			if(cv.value="?", NULL, cv.value) AS value  
		FROM 
			custom_fields cf, `custom_values` cv 
		WHERE 
			cf.id=cv.custom_field_id AND 
			cf.name="Business Value" AND 
		   `customized_type` LIKE 'Issue';

MapScrumPointsToIssue:
	Mapping
	LOAD
		customized_id AS IssueID,
		value AS ScrumPoints;
	SQL SELECT 
			cv.customized_id, 
			if(cv.value="?", NULL, cv.value) AS value  
		FROM 
			custom_fields cf, `custom_values` cv 
		WHERE 
			cf.id=cv.custom_field_id AND 
			cf.name="Scrum Points" AND 
		   `customized_type` LIKE 'Issue';


[Issues]:
LOAD 
   id AS [IssueID],
   subject AS [Issue Subject],

	ApplyMap('MapProjectIDtoProject', project_id, null()) AS [Project Name],
	ApplyMap('MapTargetVersionIDtoTargetVersion', fixed_version_id, null()) AS [Target Version],	
	ApplyMap('MapStatusIDtoStatus', status_id, null()) AS Status,
	ApplyMap('MapAssigneeIDtoAssignee', assigned_to_id, null()) as Assignee,
	ApplyMap('MapAuthorIDtoAuthor', author_id, null()) AS Author,
	ApplyMap('MapScrumPointsToIssue', id, null()) AS [Scrum Points],
	ApplyMap('MapBusinessValueToIssue', id, null()) AS [Business Value],
	`category_id`,
 `created_on` AS CreatedDate,
 description,
 `done_ratio`,
 `due_date` AS DueDate,
 `estimated_hours` AS EstimatedTime,
	`tracker_id`, 
   `updated_on` AS LastUpdated
   ;
   
   
SQL SELECT 
	`assigned_to_id`, 
	`author_id`, 
	`category_id`, 
	date_format(`created_on`, "%Y-%m-%d") AS created_on, 
	description, 
	`done_ratio`, 
	date_format(`due_date`, "%Y-%m-%d") AS `due_date`, 
	`estimated_hours`, 
	`fixed_version_id`, 
	id, 
	`is_private`, 
	lft, 
	`lock_version`, 
	`parent_id`, 
	`priority_id`, 
	`project_id`, 
	rgt, 
	`root_id`, 
	`start_date`, 
	`status_id`, 
	subject, 
	`tracker_id`, 
	`updated_on` 
	FROM redmine.issues;


[TimeEntries]:
LOAD 
id AS time_entry_id, 
`project_id` AS [ProjectID], 
`user_id` AS TimereportedUserId, 
`issue_id` AS IssueID, 
hours AS TimeUsed, 
comments, 
`activity_id`, 
`spent_on`,
 tyear, 
 tmonth, 
 tweek, 
 `created_on` AS TimeEntryCreated, 
 `updated_on`;
SQL SELECT * FROM redmine.`time_entries`;



[JournalDetails]:
LOAD 
id AS JournalDetailID, 
`journal_id` AS JournalID, 
property AS JournalProperty, 
`prop_key` AS JournalPropertyKey, 
`old_value` AS JournalOldValue,
 value AS JournalNewValue;
SQL SELECT * FROM redmine.`journal_details`;

[Journal]:
LOAD 
id AS JournalID, 
`journalized_id` AS IssueID, 
`journalized_type` AS JournalType, 
`user_id`,
 notes, 
 `created_on`;
SQL SELECT id, journalized_id, journalized_type, user_id, notes, date_format(created_on, "%Y-%m-%d") AS created_on FROM redmine.journals WHERE journalized_type="Issue";