Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I trying to merge / concatenate data from 2 different tables in a for-loop. Based on conditions I'm adding new InvoiceLines with data from Markup table. Im iterating the Order and Markup table but I think I need to get hold of the "InvoiceId" related to the current Order to be able to add the new InvoiceLines. Should I use Lookup?
Another thought, should I drop the Markup table after the for-loop?
Any help are much appreciated.
Order:
OrderId | CountryCode |
---|---|
1234 | SE |
1235 | NO |
Invoice:
InvoiceId | OrderId |
---|---|
789 | 1234 |
790 | 1234 |
791 | 1235 |
InvoiceLines:
InvoiceLineId | InvoiceId | Code | Descr | Price |
---|---|---|---|---|
45645 | 789 | CPC | Description 1 | 150 |
45871 | 789 | CPO | Description 2 | 100 |
46987 | 790 | CPK | Description 3 | 80 |
54679 | 791 | CKJ | Description 4 | 105 |
Markup:
Code | Descr | CountryCode | Price |
---|---|---|---|
HKJ | Extra product | SE | 17 |
JKB | Extra product | NO | 15 |
LET NumOfOrderRows = NoOfRows('Order');
LET NumOfMarkupRows = NoOfRows('Markup');
FOR i=0 to $(NumOfOrderRows)-1
FOR j=0 to $(NumOfMarkupRows)-1
IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order')) THEN
CONCATENATE (InvoiceLines)
LOAD 0 as InvoiceLineId,
PEEK('Code', $(j), 'Markup') as Code,
PEEK('Descr', $(j), 'Markup') as Descr,
PEEK('Price', $(j), 'Markup') as Price,
//How do I lookup a InvoiceId related to current Order?
RESIDENT Markup; //Is this necessary?
END IF
NEXT;
NEXT;
Hi,
There are 2 ways of doing this if you are loading the data into QVD's then you can use them in the below script or if you doing direct load from the source then the below script as it is will work. Please not that ultimately in you data model viewer there should be one table only with the appended data. Please confirm if that is not the requirement.
MarkupTemp:
LOAD
Code as CODE,
Descr AS DESCR,
CountryCode AS COUNTRYCODE,
Price AS PRICE
Resident Markup_table;
join(MarkupTemp)
LOAD
OrderId AS ORDERID,
CountryCode AS COUNTRYCODE
Resident Order_table;
join(MarkupTemp)
LOAD
InvoiceId AS INVOICEID,
OrderId AS ORDERID
Resident Invoice_Table;
[InvoiceLines]:
Load 0 AS INVOICELINEID,
INVOICEID,
CODE,
DESCR,
PRICE
Resident MarkupTemp;
concatenate
LOAD [InvoiceLineId] AS INVOICELINEID,
[InvoiceId]AS INVOICEID,
[Code] AS CODE,
[Descr] AS DESCR,
[Price] AS PRICE
FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
(biff, embedded labels, table is InvoiceLines$);
Drop Table MarkupTemp;
Drop Table Markup_table;
Drop Table Order_table;
Drop Table Invoice_Table;