Parsing a web table in Qlik (via REST API Connection)

bouky56 — Sat, 22 Oct 2022 15:26:00 GMT

Dear Community,

I am struggling with parsing a table in Confluence page into Qlik. Unfortunately, we cannot connect via "web" connection, but we have to connect to the Confluence page via REST API.

After connecting, the Qlik offers us the following code in Load Script:

RestConnectorMasterTable:
SQL SELECT
"__KEY_root",
(SELECT
"__FK_body",
"__KEY_body",
(SELECT
"value",
"__FK_storage",
"__KEY_storage",
(SELECT
"content",
"__FK__expandable"
FROM "_expandable" FK "__FK__expandable")
FROM "storage" PK "__KEY_storage" FK "__FK_storage")
FROM "body" PK "__KEY_body" FK "__FK_body")
FROM JSON (wrap on) "root" PK "__KEY_root";

[_expandable]:
LOAD [content],
[__FK__expandable] AS [__KEY_storage]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK__expandable]);

[storage]:
LOAD [value],
TextBetween([value], '<th>', '</th>') & '|' as "separator1",
PurgeChar([value], '<>/\') as "separator2",
replace([value], '</th>', ' |' ) as "separator3"

RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_storage]);

DROP TABLE RestConnectorMasterTable;

___________________________________________

This however reads only huge chunks of unparsed codes such as:

<h2 class="auto-cursor-target">Launched, public-facing sites</h2> <table class="wrapped"><colgroup><col style="width: 44.0px;" /><col style="width: 243.0px;" /><col style="width: 86.0px;" /><col style="width: 70.0px;" /><col style="width: 86.0px;" /><col style="width: 99.0px;" /><col style="width: 132.0px;" /><col style="width: 266.0px;" /><col style="width: 258.0px;" /><col style="width: 269.0px;" /><col style="width: 165.0px;" /><col style="width: 270.0px;" /><col style="width: 138.0px;" /><col style="width: 82.0px;" /><col style="width: 112.0px;" /><col style="width: 94.0px;" /><col style="width: 94.0px;" /></colgroup><tbody><tr><th colspan="1">#</th>
<th>Website URL</th><th colspan="1">Website Type</th><th colspan="1">Version</th><th colspan="1">Region</th><th colspan="1">Markets</th><th colspan="1">Person Responsible</th><th colspan="1">Contact Person</th><th colspan="1">Local Contacts</th><th colspan="1">Other Contacts</th><th colspan="1">Title of Other Contacts</th><th colspan="1">Publisher/Agency</th><th>Launch Date</th><th>Migrated</th><th>Site running</th><th colspan="1">NewRelic Monitor Deployed? </th><th>Notes</th></tr>
<tr><td colspan="1">1</td><td><a href="www.site1.com">site1.com</a></td><td colspan="1">Universal Theme (Microsite)</td><td colspan="1"> </td><td colspan="1">LA</td><td colspan="1">Latam</td><td colspan="1">Person1</td><td colspan="1"><a href="mailto:contact1@email.com">contact1@email.com</a> </td><td colspan="1"><a href="mailto:local1@email.com">local1@email.com</a>  </td><td colspan="1"><a href="other1@email.com">other1@email.com</a> </td> .........

___________________________________

Our desired solution is to end up with a table such as this :

Any thoughts? I will be glad to provide more details.

Many thanks in advance.

Best,
Tomas

topic Parsing a web table in Qlik (via REST API Connection) in App Development

Parsing a web table in Qlik (via REST API Connection)