Hello guys,
complete Qlik newbie here. The issue Im struggling is as follows; I'm trying to fetch invoice data and then invoice line data which are to be associated with invoice via invoice number. To fetch invoice lines for one invoice, I have to query to a specific e-conomic URL (as I'm connecting via REST). So, to fetch all invoices, I can query for https://xxx.com/invoices/booked. To query for all invoice lines of an invoice, I need to query https://xxx.com/invoices/booked/invoiceNumber. This becomes quite complicated as every client has dynamic amount of invoices and thus hard coding a for loop with the correct amount of invoices specified would be somewhat tedious solution.
I'm currently trying to count the rows of Invoice tables:
temp:
Load
[bookedInvoiceNumber] as [bookedInvoiceNumberTest]
Resident [invoice_attributes];
Let rows = NoOfRows('invoice_attributes');
Then, I'm trying to construct a loop that queries the invoice line URL for each invoice. I start by assigning the variable X the value of the invoice number that is to be queried for lines.
for i=0 to $(rows)
Let x = Peek('bookedInvoiceNumberTest', -$(i));
When I try to Trace the X variable within the loop, I get good results, it outputs correct invoice numbers (starting from the last added one). However as soon as I insert the query and load script, it refuses to work and throws weird formatting errors (regarding operators that work flawlessly in a different implementation. Im appending the query and load script just in case I made a mistake there too:
RestConnectorMasterTable:
SQL SELECT
"bookedInvoiceNumber",
(SELECT
"lineNumber",
"description",
"quantity",
"unitNetPrice",
"discountPercentage",
"unitCostPrice",
"vatRate",
"vatAmount",
"totalNetAmount",
"__KEY_lines",
"__FK_lines",
(SELECT
"productNumber",
"self" AS "self_u5",
"__FK_product"
FROM "product" FK "__FK_product")
FROM "lines" PK "__KEY_lines" FK "__FK_lines")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (URL "https://restapi.e-conomic.com/invoices/booked/$(x)");
[lines]:
LOAD
[lineNumber] AS [lineNumber],
[description] AS [Line_description],
[quantity] AS [quantity],
num#([unitNetPrice], '#.#', '.' , ',') AS [unitNetPrice],
num#([discountPercentage], '#.#', '.' , ',') AS [discountPercentage],
num#([unitCostPrice], '#.#', '.' , ',') AS [unitCostPrice],
num#([vatAmount], '#.#', '.' , ',') AS [vatAmount],
num#([totalNetAmount], '#.#', '.' , ',') AS [totalNetAmount],
$(x) as bookedInvoiceNumber,
$(x) &'|'& [lineNumber] AS [bookedInvoiceNumber_LineNumber],
$(x) &'|'& [__KEY_lines] as [__KEY_lines]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_lines]);
[product]:
LOAD
[productNumber] AS [productNumber],
$(x) &'|'& [__FK_product] as [__KEY_lines]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_product]);
DROP TABLE RestConnectorMasterTable;
From debugging, it seems that after the first loop, the X variable has NULL value and thus the loop keeps looping itself, trying to fetch lines from the general invoice URL (as the script didn't set any ID via X). This way of achieving my goal feels just dirty.
I will be eternally grateful to anyone pointing me to the right direction with this one, because I believe there is easier way to go about this, however Im unable to figure it out. Thank you.
Zprávu upravil(a): Ondřej Soukup
Clarification about the loop
