Loading using JSON API and building link between data

[Daniel77]

Hi Support, i hope you pros are able to assist and i dont have to resort to Chat GPT's constant lies!

I am loading from a JSON API for the first time  and have hit an issue with using the Name and Value Lists.

I am loading form the Name and Value fields separately and also like this to define in their own dimension.

LOAD [Name],
[Name2],
[OrgValue],
[Value],
[SrcType],
[FieldType],
[LinkGroup],
[__FK_OrderLinePrintData] AS [__KEY_OrderLines],

f([Name] = 'DROPDOWN_01',[Value]) as Category,
if([Name] = 'f_t01_01_locklang:en',[Value]) as Style.

RESIDENT RestConnectorMasterTable

My issue can be seen in the image of the table below:

The values for Style cannot be on the same line as Category.

I think i need to pivot the data with a generic load but i have not been able to successfully achieve this.

Does anyone have any information on how i can relate these fields?

thank you

Daniel

[AustinSpivey · Accepted solution]

I think a good way to handle this would be to create the [Category] and [Style] fields in a separate table that gets immediately joined back to the original table. Something like this:

Join ([Data])
Load Distinct
    [__KEY_OrderLines]
  , Only( if([Name] = 'DROPDOWN_01', [Value]) ) as [Category]
  , Only( if([Name] = 'f_t01_01_locklang:en', [Value]) ) as [Style]
Resident [Data]
  Group By [__KEY_OrderLines]
;

Here's how this works:

• We use the [__KEY_Order_Lines] field as our key to link back to the original table (the one shown in your example).
• We use the same If() expressions you wrote to create our [Category] and [Sales] fields except that we wrap them each in the Only() aggregation function. We do this because we want to return values where there's only one value to return, which is what the result would be when we group them on the [__KEY_Order_Lines] field. If this seems odd, I'd recommend this excellent article by Henric Cronstrom. Here is another article from the Qlik Help site that also discusses the function with several examples, though this one is more focused on using it in chart expressions.
• We end our table load with the requisite Group By statement, grouping our Only() aggregations on the [__KEY_Order_Lines] field.
• Note that the Load statement at the beginning includes the Distinct keyword, which will make sure that the table is loading only unique records.

This is what our new table would look like without joining it directly to our original table:

...and then here is what our final table would look like when it's directly joined, like we do in our script above:

[Daniel77]

Hey,

WOW, thank you so much for the reply, i will look into this in the morning and let you know how i got on but thanks so much for the detailed reply, really appreciated.

Daniel

[AustinSpivey]

No prob, hope it helps!

[Daniel77]

Hi Austin,

Thank you again for taking the time to explain the way the load should work, an also the links you provided, these will be so helpful.

I have tried to implement the script you provided and this has not been successful, maybe because there is more to the script load than i initially explained.

Here is the full JSON LOAD.

RestConnectorMasterTable:
SQL SELECT
"SenderSystem",
"WebOrderHeadID",
"ERPOrderNumber",
"SegmentID",
"SubSegmentID",
"LoginID",
"OrderNumber",
"ERPCustomerAccountID",
"CustomerAccountNo",
"BrandOwnerPurchaseNumber",
"BrandOwner",
"Brand",
"BrandPrefix",
"RetailerCode",
"ERPEntityID" AS "ERPEntityID_u0",
"OrderDate",
"CustomerName",
"ERPCustomerAddressID",
"CustomerAddress",
"CustomerZipCode",
"CustomerCity",
"CustomerCountry",
"CustomerCountryISO",
"ERPCustomerContactID",
"CustomerContact",
"CustomerEmail",
"CustomerPhone",
"ERPDeliveryAddressID",
"DeliveryName",
"DeliveryAddress",
"DeliveryZipCode",
"DeliveryCity",
"DeliveryCountry",
"DeliveryCountryISO",
"ERPDeliveryContactID",
"DeliveryContact",
"DeliveryEmail",
"DeliveryPhone",
"DeliveryDate",
"SupplierCode",
"Qr1",
"BrandQrLink",
"MyAtexControl",
"Requisition",
"Reference",
"__KEY_root",
(SELECT
"ERPOrderLineNumber",
"ERPSalesLineID",
"ERPEntityID",
"OrderLineNumber",
"WebOrderNumber",
"ItemRef",
"ERPItemID",
"ERPItemVariant",
"Size",
"Colour",
"Quantity",
"CarelabelPages",
"AWNumber",
"ContentNumber",
"CareNumber",
"IconNumber",
"COO",
"Configuration",
"CustomerRemark",
"ItemText",
"WebOrderLineId",
"ProdLineId",
"ProdHeadId",
"__KEY_OrderLines",
"__FK_OrderLines",
(SELECT
"Name",
"Name2",
"OrgValue",
"Value",
"SrcType",
"FieldType",
"LinkGroup",
"__FK_OrderLinePrintData"
FROM "OrderLinePrintData" FK "__FK_OrderLinePrintData")
FROM "OrderLines" PK "__KEY_OrderLines" FK "__FK_OrderLines")
FROM JSON (wrap on) "root" PK "__KEY_root";

[OrderLinePrintData]:

LOAD [Name],
[Name2],
[OrgValue],
[Value],
[SrcType],
[FieldType],
[LinkGroup],
[__FK_OrderLinePrintData] AS [__KEY_OrderLines]

RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_OrderLinePrintData]);

[OrderLines]:

LOAD [ERPOrderLineNumber],
[ERPSalesLineID],
[ERPEntityID],
[OrderLineNumber],
[WebOrderNumber],
[ItemRef],
[ERPItemID],
[ERPItemVariant],
[Size],
[Colour],
[Quantity],
[CarelabelPages],
[AWNumber],
[ContentNumber],
[CareNumber],
[IconNumber],
[COO],
[Configuration],
[CustomerRemark],
[ItemText],
[WebOrderLineId],
[ProdLineId],
[ProdHeadId],
[__KEY_OrderLines],
[__FK_OrderLines] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_OrderLines]);

[root]:
LOAD [SenderSystem],
[WebOrderHeadID],
[ERPOrderNumber],
[SegmentID],
[SubSegmentID],
[LoginID],
[OrderNumber],
[ERPCustomerAccountID],
[CustomerAccountNo],
[BrandOwnerPurchaseNumber],
[BrandOwner],
[Brand],
[BrandPrefix],
[RetailerCode],
[ERPEntityID_u0] AS [ERPEntityID_u0],
[OrderDate],
[CustomerName],
[ERPCustomerAddressID],
[CustomerAddress],
[CustomerZipCode],
[CustomerCity],
[CustomerCountry],
[CustomerCountryISO],
[ERPCustomerContactID],
[CustomerContact],
[CustomerEmail],
[CustomerPhone],
[ERPDeliveryAddressID],
[DeliveryName],
[DeliveryAddress],
[DeliveryZipCode],
[DeliveryCity],
[DeliveryCountry],
[DeliveryCountryISO],
[ERPDeliveryContactID],
[DeliveryContact],
[DeliveryEmail],
[DeliveryPhone],
[DeliveryDate],
[SupplierCode],
[Qr1],
[BrandQrLink],
[MyAtexControl],
[Requisition],
[Reference],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root]);

DROP TABLE RestConnectorMasterTable;

and then here is the new page i made to link to the data using the category and the Style and Keyorder lines.

Join([OrderLinePrintData])


Load
Distinct
[__KEY_OrderLines],

Only(if([Name] = 'DROPDOWN_01', [Value]) ) as [Category],
Only( if([Name] = 'f_t01_01_locklang:en', [Value]) ) as [Style]

Resident
[OrderLinePrintData]

Group by [__KEY_OrderLines]
;

please can you let me know if the full structure of the load could be having an impact on the final result where the Category are still dropping onto a different line.

The result in the table is as follows:

Category is Null when selecting a key order line, but in the table below we see the keyorder line is linking:

Thank you Austin, any further assistance would be amazing.

Daniel 

[Daniel77]

Hi Again Austin,

I have found a solution to the issue. I had a play and found the below worked ok by making a seperate table for the category and joining back.

Join([OrderLinePrintData])

Load
Distinct
[__KEY_OrderLines],


Only( if([Name] = 'f_t01_01_locklang:en', [Value]) ) as [Style]

Resident
[OrderLinePrintData]

Group by [__KEY_OrderLines]
;
 ////////New section//////

Join([OrderLinePrintData])

Load
Distinct
[__KEY_OrderLines],

if([Name] = 'DROPDOWN_01', [Value]) as [Category]

Resident
[OrderLinePrintData];

Thank you again for the detail and time you have put into this request, i can honestly say this is the most comprehensive reply i have ever received.

Austin 5 ....ChatGPT  nill!

Thank you again, 

Daniel

[AustinSpivey]

Glad the solution helped! Always happy to help, ChatGPT has much to "learn" before it will understand the idiosyncrasies of Qlik (for now)!