xbrl_beta_api_qlik_load_script.qvs

//This "Data connection" (in Qlik) in our "Library" represent sthe REST
// https://betaapi.xbrl.us/api/v1/fact/search?entity.cik=0001138723&concept.local-name=Assets&period.fiscal-period=Y&period.fiscal-year=2016,2015,2014&fields=fact.*
//Is this necessary? Yes, but I don't know why;
//I overwrite most of its configurations (the URL, the query parameters, the HTTP headers), below
//Without a data connection, Qlik logs:
// > The following error occurred:
// > There is no open data connection.
LIB CONNECT TO 'xbrl_api (qtsel_nnd)';


//See the XBRL API Documentation
//You must "obtain authorization" at the XBRL US website;
//This means generating a one-time client ID and client secret, (I have generated my own; I cannot do yours)
//With this client_id and client_secret you can send a POST Authorization request,
//and the authorization response includes a temporary access_token (and refresh_token)
//That response access_token is used in each subsequent HTTP request, with the HTTPHEADER authorization "Basic $(access_token)"

//Nate uses Insomnia (as recommended in the XBRL API documentation) to get the access_token
//Then he writes the value here:
//TODO: Update this Qlik script to do the authorization , retrieve & store access_token, and refresh the token if needed;
SET access_token = 90298021-7670-4934-8098-724796f72a18;

//https://xbrl.us/wp-content/uploads/2018/03/XBRLAPI-v1.pdf

//https://xbrl.us/wp-content/uploads/2018/03/XBRLAPI-v1.pdf

LET cumulative_loaded_row_count = 0;

//See XBRL API documentation re: "Handling Paging"
//Nate *thinks* the XBRL API will only return 1000 rows at a time
//So Nate only *asks* for 1000 rows at *a time*
//however, Nate asks *multiple times* = "paging" to accumulate 1000 rows each time:
LET page_size = 1000;

LET last_loaded_row_count = 0;

//Don't want to abuse my XBRL API privileges
//TODO: Store/cache into QVD for later usage
//and/or use ETL process *before* the Qlik layer, to retrieve and store data
LET max_num_rows_to_load = 10000;

SET xbrl_beta_api_fact_url = https://betaapi.xbrl.us/api/v1/fact/search;
SET xbrl_concept_local_name = Assets;
//"Y" for "Year"?
SET xbrl_period_fiscal_period = Y;
SET xbrl_period_fiscal_year = 2017;
SET xbrl_fact_has_dimensions = f;
SET xbrl_fact_ultimus = 1;

//6311 represents Life Insurance
//https://www.osha.gov/pls/imis/sicsearch.html?p_sic=&p_search=insurance
//https://www.osha.gov/pls/imis/sic_manual.display?id=86&tab=description
SET xbrl_report_sic_code = 6311;


//Read more about loops in Qlik load scripts here
//https://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script
//Specifically the Do While script
//https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptControlStatements/do.htm
DO 

//WRONG:
//       QUERY "fact.is-ultimus" "$(...)",
//CORRECT:
//       QUERY "fact.ultimus" "$(...)",
//       QUERY "fact.is-extended" "$(...)",


//REMOVED, tried these:
//       QUERY "period.fiscal-period" "$(xbrl_period_fiscal_period)",
//       QUERY "period.fiscal-year" "$(xbrl_period_fiscal_year)",
// 	     QUERY "entity.cik"  "0001138723"
  SET xbrl_with_connection = WITH CONNECTION(URL "$(xbrl_beta_api_fact_url)",
      HTTPHEADER "authorization" "Bearer $(access_token)",
      QUERY "concept.local-name" "$(xbrl_concept_local_name)",
      QUERY "fields" "fact.*,fact.limit($(page_size)),fact.offset($(cumulative_loaded_row_count))",
      QUERY "fact.has-dimensions" "$(xbrl_fact_has_dimensions)",
      QUERY "report.sic-code" "$(xbrl_report_sic_code)",
      QUERY "fact.ultimus" "$(xbrl_fact_ultimus)");

	//TRACE command will write a line to the logs which accumulate as Qlik tries to *run* this load script (i.e. click "Load Data" button)
    //So this is just for my information:
  TRACE Querying fields $(xbrl_with_connection);

  // For more information:
  // https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Create-REST-connection/Create-REST-connection.htm
  // > "REST Connector load scripts consist of A) one SELECT statement 
  // > B) and one or more LOAD statements."

  //May be short on documentation:
  //https://community.qlik.com/thread/248702

  // https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Load-REST-data/Load-data.htm#anchor-5
  // > "Query parameters and headers included in the WITH CONNECTION statement 
  // > take precedence over parameters and headers in the Create new connection (REST) dialog."

  // > "If the POST method is used, the body of text is taken from the WITH CONNECTION statement. 
  // > If the WITH CONNECTION statement does not contain a Body property the text is taken from 
  // > the text specified in the Request body field in the Create new connection (REST) dialog."


  //Other struggles : https://community.qlik.com/thread/288520

  // https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/REST-connector.htm


	//How did I know all these columns?
    //Did I write this SQL part by hand?
    // No, I used the "Data Connection" UI in Qlik to create my first XBRL BETA API REST connection
    // It remains a simple configuration:  only to 1) A URL (in my first case the /api/v1/fact/search endpoint), and 2) an Authentication token (which has since expired)
    // After "Test Connection" to ensure it works, I Save/Close the Data Connection, then using its "Select Data" icon in the Data connections panel on the right
    //I can choose the JSON to load; Qlik automatically creates the correct A) SQL part of this REST connector connection:

  RestConnectorMasterTable:
  SQL SELECT 
      "__KEY_root",
      (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"
  $(xbrl_with_connection);

  //This syntax is how Qlik allows
  //Despite the Data Connection which specified

  //Note the "bearer" authorization https://security.stackexchange.com/questions/108662/why-is-bearer-required-before-the-token-in-authorization-header-in-a-http-reAdd 
  //Which in Insomnia is:
  // Its own tab, (vs not lumped under the "Header" tab where *other* HTTP headers are)
  // And uses a "Tag" which means the value will be dynamically populated; "Response - reference values from  another requests's response"
  // The Body and JSONPath


	//I changed the automatically created Qlik script by using "*" for simplicity / avoid redundancy
    //We only rename one thing using  " AS " ; that is the __FK_data AS __KEY_root , which is necessary to "JOIN" the data if it is hierarchical (It isn't)
  [fact]:
  LOAD	
  		*,
        'N/A' as SO_NO_UNION
  RESIDENT RestConnectorMasterTable
  WHERE NOT ISNULL(__FK_data);



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

  DROP TABLE RestConnectorMasterTable;
  DROP FIELD '__FK_data';
  DROP FIELD '__KEY_root';
  DROP FIELD 'SO_NO_UNION';
  //I dont know where this comes from:
  DROP FIELD '__extra_';
 // DROP TABLE paging;
  //Was exploring: DROP TABLE data;
  

  TRACE ---------------------------------------;
  IF LEN(last_loaded_row_count)=0 OR ISNULL(last_loaded_row_count) THEN
  	TRACE Your XBRL API token is probably expired;
  ELSE
  	TRACE XBRL API Cumulative Rows: $(cumulative_loaded_row_count) Current Rows: $(last_loaded_row_count);
  ENDIF

  
LOOP WHILE (last_loaded_row_count >= page_size AND cumulative_loaded_row_count < max_num_rows_to_load);








SET xbrl_beta_api_entity_url = https://betaapi.xbrl.us/api/v1/entity/report/search;


RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"entity.cik",
		"entity.id",
		"entity.name",
		"entity.scheme",
		"entity.ticker",
		"__KEY_data",
		"__FK_data"
	FROM "data" PK "__KEY_data" FK "__FK_data")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "$(xbrl_beta_api_entity_url)",
      HTTPHEADER "authorization" "Bearer $(access_token)",
      QUERY "fields" "entity.*");

[entity]:
LOAD	[entity.cik] AS [entity.cik],
	[entity.id] AS [entity.id],
	[entity.name] AS [entity.name],
	[entity.scheme] AS [entity.scheme],
	[entity.ticker] AS [entity.ticker],
	[__KEY_data] AS [__KEY_data],
	[__FK_data] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_data]);


DROP TABLE RestConnectorMasterTable;