I have created a REST API connection with Quickbooks Online and fetching the General Ledger report by using GET method. The response tables are such that it is not at all understandable and all the values that I need are distorted and disarranged in the data model. I am not able to understand the column headers and their corresponding values.
Is there a way to get the response in such a way that the data is readable and analyzable? Or is there a way to convert the script to a normal Qliksense table script. Here's a sample of the script that i am having as a response to my Rest API call-
RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"Time",
"ReportName",
"DateMacro",
"ReportBasis",
"StartPeriod",
"EndPeriod",
"Currency",
"__KEY_Header",
"__FK_Header",
(SELECT
"Name",
"Value",
"__FK_Option"
FROM "Option" FK "__FK_Option")
FROM "Header" PK "__KEY_Header" FK "__FK_Header"),
(SELECT
"__KEY_Columns",
"__FK_Columns",
(SELECT
"ColTitle",
"ColType",
"__KEY_Column",
"__FK_Column",
(SELECT
"Name" AS "Name_u0",
"Value" AS "Value_u0",
"__FK_MetaData"
FROM "MetaData" FK "__FK_MetaData")
FROM "Column" PK "__KEY_Column" FK "__FK_Column")
FROM "Columns" PK "__KEY_Columns" FK "__FK_Columns"),
(SELECT
"__KEY_Rows",
"__FK_Rows",
(SELECT
"type" AS "type_u0",
"__KEY_Row",
"__FK_Row",
(SELECT
"__KEY_Header_u0",
"__FK_Header_u0",
(SELECT
"value",
"id",
"__FK_ColData"
FROM "ColData" FK "__FK_ColData")
FROM "Header" PK "__KEY_Header_u0" FK "__FK_Header_u0"),
(SELECT
"__KEY_Rows_u0",
"__FK_Rows_u0",
(SELECT
"type",
"__KEY_Row_u0",
"__FK_Row_u0",
(SELECT
"value" AS "value_u0",
"id" AS "id_u0",
"__FK_ColData_u0"
FROM "ColData" FK "__FK_ColData_u0")
FROM "Row" PK "__KEY_Row_u0" FK "__FK_Row_u0")
FROM "Rows" PK "__KEY_Rows_u0" FK "__FK_Rows_u0"),
(SELECT
"__KEY_Summary",
"__FK_Summary",
(SELECT
"value" AS "value_u1",
"__FK_ColData_u1"
FROM "ColData" FK "__FK_ColData_u1")
FROM "Summary" PK "__KEY_Summary" FK "__FK_Summary")
FROM "Row" PK "__KEY_Row" FK "__FK_Row")
FROM "Rows" PK "__KEY_Rows" FK "__FK_Rows")
FROM JSON (wrap on) "root" PK "__KEY_root";
[Option]:
LOAD [Name],
[Value],
[__FK_Option] AS [__KEY_Header]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Option]);
[Header]:
LOAD [Time],
[ReportName],
[DateMacro],
[ReportBasis],
[StartPeriod],
[EndPeriod],
[Currency],
[__KEY_Header],
[__FK_Header] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Header]);
[MetaData]:
LOAD [Name_u0] AS [Name_u0],
[Value_u0] AS [Value_u0],
[__FK_MetaData] AS [__KEY_Column]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_MetaData]);
[Column]:
LOAD [ColTitle],
[ColType],
[__KEY_Column],
[__FK_Column] AS [__KEY_Columns]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Column]);
[Columns]:
LOAD [__KEY_Columns],
[__FK_Columns] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Columns]);
[ColData]:
LOAD [value],
[id],
[__FK_ColData] AS [__KEY_Header_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_ColData]);
[Header_u0]:
LOAD [__KEY_Header_u0],
[__FK_Header_u0] AS [__KEY_Row]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Header_u0]);
[ColData_u0]:
LOAD [value_u0] AS [value_u0],
[id_u0] AS [id_u0],
[__FK_ColData_u0] AS [__KEY_Row_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_ColData_u0]);
[Row]:
LOAD [type],
[__KEY_Row_u0],
[__FK_Row_u0] AS [__KEY_Rows_u0]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Row_u0]);
[Rows]:
LOAD [__KEY_Rows_u0],
[__FK_Rows_u0] AS [__KEY_Row]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Rows_u0]);
[ColData_u1]:
LOAD [value_u1] AS [value_u1],
[__FK_ColData_u1] AS [__KEY_Summary]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_ColData_u1]);
[Summary]:
LOAD [__KEY_Summary],
[__FK_Summary] AS [__KEY_Row]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Summary]);
[Row_u0]:
LOAD [type_u0] AS [type_u0],
[__KEY_Row],
[__FK_Row] AS [__KEY_Rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Row]);
[Rows_u0]:
LOAD [__KEY_Rows],
[__FK_Rows] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_Rows]);
[root]:
LOAD [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
