xbrl_into_qlik

LIB CONNECT TO 'xbrl_api_fact (qtsel_nnd)';

// Action required: Implement the logic to retrieve the total records 
// from the REST source and assign to the 'total' local variable.
Let total = 3000;

Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 1000;

LET count = 1000;

//Removing for:
// for startAt = 0 to total step pageSize

LET dontGetMoreRowsThanThisDontWantToMakeThemAngry = 30000;
//Adding do-while instead:
// https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlStatements/do.htm
//  > Do [ ( while | until ) condition ] [statements]
//  > [exit do [ ( when | unless ) condition ] [statements]
//  > loop[ ( while | until ) condition ]
Do while (count = pageSize AND totalfetched < dontGetMoreRowsThanThisDontWantToMakeThemAngry)
// in:
// LOAD * INLINE [
// Val
// 1];

TRACE Querying fields as: "fields" "fact.*,fact.limit($(pageSize)),fact.offset($(startAt))");

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"limit",
		"offset",
		"count",
		"__FK_paging"
	FROM "paging" FK "__FK_paging"),
	(SELECT 
		"concept.balance-type",
		"concept.datatype",
		"concept.id",
		"concept.is-base",
		"concept.is-monetary",
		"concept.local-name",
		"concept.namespace",
		"concept.period-type",
		"dimensions",
		"dimensions.count",
		"dts.entry-point",
		"dts.id",
		"dts.target-namespace",
		"entity.cik",
		"entity.id",
		"entity.name",
		"entity.scheme",
		"fact.decimals",
		"fact.has-dimensions",
		"fact.hash",
		"fact.id",
		"fact.is-extended",
		"fact.numerical-value",
		"fact.ultimus",
		"fact.ultimus-index",
		"fact.value",
		"fact.xml-id",
		"period.calendar-period",
		"period.end",
		"period.fiscal-id",
		"period.fiscal-period",
		"period.fiscal-year",
		"period.id",
		"period.instant",
		"period.start",
		"period.year",
		"report.accession",
		"report.creation-software",
		"report.entry-url",
		"report.filing-date",
		"report.id",
		"report.period-end",
		"report.restated",
		"report.restated-index",
		"report.sec-url",
		"report.sic-code",
		"report.type",
		"unit",
		"unit.denominator",
		"unit.numerator",
		"unit.qname",
		"__FK_data"
	FROM "data" FK "__FK_data")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "https://betaapi.xbrl.us/api/v1/fact/search",
HTTPHEADER "authorization" "Bearer d59d5f2e-abeb-4d1e-a5e7-504fac2c2da1",
QUERY "concept.local-name" "Assets",
QUERY "period.fiscal-period" "Y",
QUERY "period.fiscal-year" "2017",
QUERY "fields" "fact.*,fact.limit($(pageSize)),fact.offset($(startAt))",
QUERY "fact.has-dimensions" "f",
QUERY "fact.is-ultimus" "t");

[paging]:
LOAD	[limit] AS [limit],
	[offset] AS [offset],
	[count] AS [count],
	[__FK_paging] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_paging]);


[data]:
LOAD	[concept.balance-type] AS [concept.balance-type],
	[concept.datatype] AS [concept.datatype],
	[concept.id] AS [concept.id],
	[concept.is-base] AS [concept.is-base],
	[concept.is-monetary] AS [concept.is-monetary],
	[concept.local-name] AS [concept.local-name],
	[concept.namespace] AS [concept.namespace],
	[concept.period-type] AS [concept.period-type],
	[dimensions] AS [dimensions],
	[dimensions.count] AS [dimensions.count],
	[dts.entry-point] AS [dts.entry-point],
	[dts.id] AS [dts.id],
	[dts.target-namespace] AS [dts.target-namespace],
	[entity.cik] AS [entity.cik],
	[entity.id] AS [entity.id],
	[entity.name] AS [entity.name],
	[entity.scheme] AS [entity.scheme],
	[fact.decimals] AS [fact.decimals],
	[fact.has-dimensions] AS [fact.has-dimensions],
	[fact.hash] AS [fact.hash],
	[fact.id] AS [fact.id],
	[fact.is-extended] AS [fact.is-extended],
	[fact.numerical-value] AS [fact.numerical-value],
	[fact.ultimus] AS [fact.ultimus],
	[fact.ultimus-index] AS [fact.ultimus-index],
	[fact.value] AS [fact.value],
	[fact.xml-id] AS [fact.xml-id],
	[period.calendar-period] AS [period.calendar-period],
	[period.end] AS [period.end],
	[period.fiscal-id] AS [period.fiscal-id],
	[period.fiscal-period] AS [period.fiscal-period],
	[period.fiscal-year] AS [period.fiscal-year],
	[period.id] AS [period.id],
	[period.instant] AS [period.instant],
	[period.start] AS [period.start],
	[period.year] AS [period.year],
	[report.accession] AS [report.accession],
	[report.creation-software] AS [report.creation-software],
	[report.entry-url] AS [report.entry-url],
	[report.filing-date] AS [report.filing-date],
	[report.id] AS [report.id],
	[report.period-end] AS [report.period-end],
	[report.restated] AS [report.restated],
	[report.restated-index] AS [report.restated-index],
	[report.sec-url] AS [report.sec-url],
	[report.sic-code] AS [report.sic-code],
	[report.type] AS [report.type],
	[unit] AS [unit],
	[unit.denominator] AS [unit.denominator],
	[unit.numerator] AS [unit.numerator],
	[unit.qname] AS [unit.qname],
	[__FK_data] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);


// https://community.qlik.com/thread/49432#201875
LET count = PEEK('count', 0, 'paging');

TRACE Count of rows from XBRL API is : $(count);

DROP TABLE RestConnectorMasterTable;
DROP TABLE paging;
//TODO: Understand 

//No longer incrementing with a "for next";
//Therefore increment manually:
LET startAt = startAt + pageSize;
LET totalfetched = totalfetched + count;
// LET startAt = $(startAt) + $(pageSize);


//Removing "next" becuase not using "for"
//NEXT startAt;

// Instead loop:
LOOP;



EXIT SCRIPT;