Rest Connection & Cross table

[Antony3]

Hi all,

Background
I have used a rest connection to extract currency information. Found here if it helps in anyone in anyway - https://www.exchangerate-api.com/docs/python-currency-api. 

Issue
Currently the different currencies are being loaded as separate fields so my tables are 2 rows longs with roughly 180 odd columns. I believe based on the documentation its coming through as a JSON but my working experience with json is limited. 

Question

How do I use cross table (or another way) to change the load script so that it loads as one field. I have attached a picture for reference of what my python extract API is currently creating. 

Current Qlik load script

RestConnectorMasterTable:

SQL SELECT 

"result",

"documentation",

"terms_of_use",

"time_last_update_unix",

"time_last_update_utc",

"time_next_update_unix",

"time_next_update_utc",

"base_code",

"__KEY_root",

    (SELECT 

"GBP",

"AED",

"AFN",

...

"ZAR",

"ZMW",

"ZWL",

"__FK_conversion_rates"

FROM "conversion_rates" FK "__FK_conversion_rates")

FROM JSON (wrap on) "root" PK "__KEY_root";

[conversion_rates]:

LOAD

[GBP],

[AED],

[AFN],

...

[ZAR],

[ZMW],

[ZWL],

[__FK_conversion_rates] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_conversion_rates]);

[root]:

LOAD [result],

[documentation],

[terms_of_use],

[time_last_update_unix],

[time_last_update_utc],

[time_next_update_unix],

[time_next_update_utc],

[base_code],

[__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

Test:

crosstable (time_last_update_utc, [base_code]) LOAD * resident root;

DROP TABLE RestConnectorMasterTable;

Drop Table root;

[Antony3 · Accepted solution]

For anyone that may be interested. This was the final solution (I have removed currencies to make it easier to read). Big shout out to Peter Webb at Websy Academy for the help. 

RestConnectorMasterTable:

SQL SELECT 

"result",

"documentation",

"terms_of_use",

"time_last_update_unix",

"time_last_update_utc",

"time_next_update_unix",

"time_next_update_utc",

"base_code",

"__KEY_root",

    (SELECT 

"GBP",

"AED",

"AFN",

"ALL",

...

"ZAR",

"ZMW",

"ZWL",

"__FK_conversion_rates"

FROM "conversion_rates" FK "__FK_conversion_rates")

FROM JSON (wrap on) "root" PK "__KEY_root";

// Create to Order Columns correctly - this will be your from your source, not my test data

Temp:

LOAD

    result,

    documentation,

    terms_of_use,

    time_last_update_unix,

    time_last_update_utc,

    time_next_update_unix,

    time_next_update_utc,

    base_code,

    __KEY_root,

Date(today(),'DD/MM/YYYY') as Calender_key,

"GBP",

"AED",

"AFN",

...

"ZAR",

"ZMW",

"ZWL"

resident RestConnectorMasterTable;

// Perform Crosstable

// - Currency is the new column heading for the currency type

// - Rate is the new column heading for the currency rate

// - 10 is the number of column thet appear first that need to stay constant, not unpivoted

Cross_Table:

CrossTable(Currency, Rate, 10)

LOAD

    result,

    documentation,

    terms_of_use,

    time_last_update_unix,

    time_last_update_utc,

    time_next_update_unix,

    time_next_update_utc,

    base_code,

    __KEY_root,

Date(today(),'DD/MM/YYYY') as "Data Date",

"GBP",

"AED",

"AFN",

...

"ZAR",

"ZMW",

"ZWL"

Resident Temp;

Drop Table Temp;

DROP TABLE RestConnectorMasterTable;