Hi,
I'm trying to concatenate to tables when certain conditions match (Category, Code, Countrycode and Dates in the Markup table).
Everything seems to work as it should in the app but the output in the load data-window says: InvoiceLines << Markup, Lines fetched, thousand times. The value also increase for every row in the output window. Am I missing something crucial?
Any help are appreciated.
The Order table contains approx 300 000 rows and the markup table contains about 150 rows. Is the nested for-loop the best solutions if I need to check multiple conditons (category, dates, code, country) before concatenation?
Order:
|
| 1234 | SE | GOOGLE | 330 | 2016-01-05 |
| 1235 | NO | NEWSLETTER | 105 | 2016-01-15 |
Invoice:
InvoiceLines:
|
| 789 | CPC | Description 1 | 150 | SEK |
| 789 | CPO | Description 2 | 100 | SEK |
| 790 | CPK | Description 3 | 80 | SEK |
| 791 | CKJ | Description 4 | 105 | SEK |
Markup:
|
| GENERAL | ALL | Text1 - internal | SE | 17 | 2016-01-01 | 2016-01-01 |
| GENERAL | ALL | Text2 - internal | NO | 15 | 2015-01-05 | 2015-12-31 |
| REFERER | GOOGLE | Fee | SE | 0.1 | 2015-05-01 | 2016-12-31 |
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') AND PEEK('Category', $(j), 'Markup') = 'REFERER' AND PEEK('Code', $(j), 'Markup') =PEEK('Referer',$(i), 'Order')) THEN
LOAD
Applymap('OrderInvoiceKeyMap', PEEK('OrderId',$(i), 'Order')) as InvoiceId,
PEEK('Code', $(j), 'Markup') as Code,
PEEK('Descr', $(j), 'Markup') as Descr,
PEEK('Price', $(j), 'Markup') as Price,
IF (PEEK('CountryCode', $(i), 'Order') = 'SE', 'SEK',
IF (PEEK('CountryCode', $(i), 'Order') = 'DE', 'EUR', 'Unknown')) as Currency,
RESIDENT Markup;
END IF
NEXT j;
NEXT i;
(I use Applymap to get hold off the first InvoiceId for every Order)
