Skip to main content
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
Partner Ambassador
Partner Ambassador

Microsoft is launching a new export format for Azure Logging in the next weeks ( and guess what, Power BI can already natively import this, so I wanted Qlik to read this, too.

The result of the Kusto POST-Call comes as JSON (see here for a sample) but the data model created by the Qlik's REST Connector "select data" wizard was sub-optimal: All values of all tables come in just one column, while you hoped to get n tables with x columns each. So I had to massage the initial data returned.

I spare you the details on how I made the script, you can check yourself in the attached .qvf file if you like. Basically, I had to

  • join tablename information from the root table down to other tables and
  • to add an auto-increment number in two tables which restarts at 1 when the foreign key changes

This is what the REST-Connector Wizard creates for you ...

2019-03-01 15_55_04-Qlik Sense Desktop.png

After my massage with Qlik Scripting I have added necessary fields:

2019-03-01 15_56_41-Qlik Sense Desktop.png

My script computes the correct ultimate load-statement itself, something like this (dont worry if you can't read it, it "pivots" rows into columns 🙂

Only(If(GoesToColumn = 1, @Value)) AS [Timestamp],
Only(If(GoesToColumn = 2, @Value)) AS [ClientRequestId],
Only(If(GoesToColumn = 3, @Value)) AS [ActivityId],
Only(If(GoesToColumn = 4, @Value)) AS [SubActivityId],
Only(If(GoesToColumn = 5, @Value)) AS [ParentActivityId],
Only(If(GoesToColumn = 6, @Value)) AS [Level],
Only(If(GoesToColumn = 7, @Value)) AS [LevelName],
Only(If(GoesToColumn = 8, @Value)) AS [StatusCode],
Only(If(GoesToColumn = 9, @Value)) AS [StatusCodeName],
Only(If(GoesToColumn = 10, @Value)) AS [EventType],
Only(If(GoesToColumn = 11, @Value)) AS [EventTypeName],
Only(If(GoesToColumn = 12, @Value)) AS [Payload]
WHERE Rows.TableName = '$(vTable)'


Finally, I drop all the tables created by the REST Connector wizard, to get this nice to read format:

2019-03-01 16_02_52-Qlik Sense Desktop.png

1 Comment
Partner - Contributor
Partner - Contributor

Hi Sample data is not available at your link.

I m trying to fetch azure demo app data  in qlik , Rest wizard generating script without root table.

Please find below response 

"name" AS "name_u0",
FROM "columns" FK "__FK_columns"),
FROM "rows" FK "__FK_rows_u0" ArrayValueAlias "@Value")
FROM "rows" PK "__KEY_rows" FK "__FK_rows")
FROM JSON (wrap off) "tables" PK "__KEY_tables";


LOAD [name],
[__FK_columns] AS [__KEY_tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columns]);

LOAD [@Value],
[__FK_rows_u0] AS [__KEY_rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows_u0]);

LOAD [__KEY_rows],
[__FK_rows] AS [__KEY_tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows]);

LOAD [name_u0] AS [name_u0],
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_tables]);

DROP TABLE RestConnectorMasterTable;