// ======================== Begin Environment Variables =========================
// ======================== Locale - Numbers ====================================
Set DecimalSep		=',';
Set ThousandSep		=' ';
Set MoneyDecimalSep	=',';
Set MoneyThousandSep=' ';
Set MoneyFormat		='#,##0€;-#,##0€';
Set NumericalAbbreviation=	'3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;
							-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
 
// ======================== Locale - Date and Time ==============================
Set DateFormat		='YYYY-MM-DD';
Set TimeFormat		='hh:mm:ss';
Set TimestampFormat	='YYYY-MM-DD hh:mm:ss[.fff]';
Set TimeZoneName 	= 'Europe/Stockholm';
  
// ======================== Locale - Calendar ===================================
Set FirstWeekDay	=0;
Set BrokenWeeks		=0;
Set ReferenceDay	=4;
Set FirstMonthOfYear=1;
Set MonthNames		='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
Set LongMonthNames	=	'January;February;March;April;May;June;July;
						 August;September;October;November;December';
Set DayNames		='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Set LongDayNames	='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
	
// ======================== Locale - Sort and Search ============================
Set CollationLocale	='en-GB';
Set CreateSearchIndexOnReload=1;

// ======================== Relative paths ==================================
// To simplify the script, I use relative paths defined through a 'Directory' 
// statement. Comment the one you don't need.
// ==========================================================================
Directory C:\PathToFiles\ ;   	// QlikView
Directory lib://DataFiles/ ;   	// Qlik Sense

// ======================== Transaction tabe ================================
// This is just a mock-up for the example. In real life, you need to remove  
// this and load the real fact table.
// ==========================================================================
Transactions:
Load 
	NodeID,
	Round(10*rand()) 				as Production
FROM [Winedistricts.txt] (txt, utf8, embedded labels, delimiter is ',', msq);

// ======================== Nodes ===========================================
// Generate Expanded Nodes view of the Wine district nodes; 
// i.e. keep original table, but add one field per level in the hierarchy
// ==========================================================================
WineDistricts:
Hierarchy (NodeID,ParentID,District,ParentDistrict,District,Path,'/',Depth)
LOAD 
	NodeID 							as NodeID, 
	If(Len(ParentID),ParentID) 		as ParentID,
	Name 							as District
FROM [Winedistricts.txt] (txt, utf8, embedded labels, delimiter is ',', msq);

// ======================== Trees ===========================================
// An Ancestor table, i.e. a table with one record per Ancestor-Descendant 
// relation. Each ancestor will represent a District with ALL its sub-nodes
// ==========================================================================
WineDistrictsSubNodes:
HierarchyBelongsTo (NodeID, ParentID, DistrictCopy, TreeID, Tree)
Load
	NodeID							as NodeID, 
	ParentID 						as ParentID, 
	District 						as DistrictCopy,
	Path 							as PathCopy,
	Depth 							as DepthCopy
Resident WineDistricts ;

// ======================== Semantic Parents  ===============================
// Semantic table creating links to direct parents and direct children
// ==========================================================================
[SemanticParents]:
Semantic 
Load distinct
	NodeID 							as TreeID, 
	ParentDistrict 					as Parents,
	ParentID 						as TreeIDCopy, 
	District 						as Daughters
Resident WineDistricts 
	Where 	Len(Trim(NodeID)) > 0 
		and Len(Trim(ParentID)) > 0 
		and NodeID <> ParentID
	Order By Depth;

// ======================== Semantic Ancestors ==============================
// Semantic table creating links to all ancestors and all descendents
// ==========================================================================
[SemanticAncestors]:
Semantic 
Load distinct
	NodeID 							as TreeID, 
	Tree 							as BelongsTo,
	TreeID 							as TreeIDCopy, 
	PathCopy 						as SubDistricts 	
Resident WineDistrictsSubNodes 
	Where 	Len(Trim(NodeID)) > 0 
		and Len(Trim(TreeID)) > 0 
		and NodeID <> TreeID
	Order By DepthCopy;
	
// ======================== Copy of TreeID ==================================
// Add a copy of the tree ID in the Ancestors table. 
// (Needed for the Semantic Loads)
// ==========================================================================
Inner Join (WineDistrictsSubNodes)
Load distinct
	TreeID, 
	TreeID 							as TreeIDCopy
Resident WineDistrictsSubNodes ;

// ======================== Clean Up ========================================
Drop Field PathCopy, DepthCopy, DistrictCopy ;