Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
OrderId | CountryCode | Referer | Total | OrderDate |
---|---|---|---|---|
1234 | SE | 330 | 2016-01-05 | |
1235 | NO | NEWSLETTER | 105 | 2016-01-15 |
Invoice:
InvoiceId | OrderId |
---|---|
789 | 1234 |
790 | 1234 |
791 | 1235 |
InvoiceLines:
InvoiceId | Code | Descr | Price | Currency |
---|---|---|---|---|
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:
Category | Code | Descr | CountryCode | Price | FromDate | ToDate |
---|---|---|---|---|---|---|
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 | 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)
You should be able to do this with a simple WHERE clause:
Order:
LOAD OrderId,
CountryCode,
Referer,
Total,
OrderDate
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @1);
Invoice:
LOAD InvoiceId,
OrderId
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @2);
InvoiceLine:
LOAD InvoiceId,
Code,
Descr,
Price,
Currency
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @3);
Markup:
LOAD Category,
Code,
Descr,
CountryCode,
Price,
FromDate,
ToDate
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @4);
Concatenate
LOAD * INLINE [
Category, Code, Descr, CountryCode, Price, FromDate, ToDate
REFERER, NEWSLETTER, Fee 2, NO, 10, 2016-01-01, 2016-01-16
REFERER, NEWSLETTER, Fee 3, NO, 11, 2016-01-01, 2016-01-15
REFERER, NEWSLETTER, Fee 4, NO, 12, 2016-01-15, 2016-01-30
REFERER, NEWSLETTER, Fee 5, NO, 13, 2016-01-16, 2016-01-30
];
TMP:
NOCONCATENATE
LOAD * RESIDENT Order;
INNER JOIN
LOAD
//Category,
Code as Referer,
Code,
Descr,
CountryCode,
Price,
FromDate,
ToDate,
IF (CountryCode = 'SE', 'SEK',
IF (CountryCode = 'DE', 'EUR', 'Unknown')) as Currency
Resident Markup
WHERE Category = 'REFERER';
Concatenate (InvoiceLine)
LOAD
Applymap('OrderInvoiceKeyMap', OrderId) as InvoiceId,
Code,
Descr,
Price,
Currency
RESIDENT TMP
WHERE OrderDate >= FromDate and OrderDate < ToDate;
DROP TABLE TMP;
DROP table Markup;
Not sure if I understand what you are trying to do, but this
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;
will basically append 150 identical rows (number of Markup records), while I assume you only want to append a single line:
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
Autogenerate 1;
Thanks swuehl for trying to help out.
Im trying to add data from the Markup-table as extra invoicelines to every Order if the criteria in the If-statement are met. Every order will have approx 3-5 extra invoicelines added.
Your solution also seems to work but the data load window stills output the following thousand times:
InvoiceLines << AUTOGENERATE(1)
Lines fetched: 6 147 (increase +1 for every message)
But the total number of appended records should be much smaller then?
I think it's default behaviour that you get a script output line per table load (and that's what you are doing within the two nested FOR loops).
I am not quite sure what your exact concern is, could you detail that a bit more?
Thanks again swehel.
Yes, I seems like the number of appended rows are much smaller.
My question is if this is a good way of solving this problem? Could I append this markup rows to the InvoiceLines table in another way?
Should be possible, mayb using something along these lines:
TMP:
NOCONCATENATE
LOAD * RESIDENT Order;
INNER JOIN
LOAD
//Category,
Code as Referer,
Code,
Descr,
CountryCode,
Price,
// FromDate,
// ToDate,
IF (CountryCode = 'SE', 'SEK',
IF (CountryCode = 'DE', 'EUR', 'Unknown')) as Currency
Resident Markup
WHERE Category = 'REFERER';
Concatenate (InvoiceLine)
LOAD
Applymap('OrderInvoiceKeyMap', OrderId) as InvoiceId,
Code,
Descr,
Price,
Currency
RESIDENT TMP;
DROP TABLE TMP;
//
DROP table Markup;
This maybe could be a better solution, but how do I make sure that the Order date are between from/to date in the markup table before joining?
Where in your existing posted code are you taking care of that?
My thought was to add it to the IF-statement like this:
IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order') AND PEEK('Category', $(j), 'Markup') = 'REFERER' AND PEEK('Code', $(j), 'Markup') =PEEK('Referer',$(i), 'Order') AND DATEDIFF(day, PEEK('FromDate', $(j), 'Markup'), PEEK('OrderDate'', $(i), 'Order'))>=0 AND DATEDIFF(day, PEEK('ToDate', $(j), 'Markup'), PEEK('OrderDate'', $(i), 'Order'))<0) THEN
You should be able to do this with a simple WHERE clause:
Order:
LOAD OrderId,
CountryCode,
Referer,
Total,
OrderDate
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @1);
Invoice:
LOAD InvoiceId,
OrderId
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @2);
InvoiceLine:
LOAD InvoiceId,
Code,
Descr,
Price,
Currency
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @3);
Markup:
LOAD Category,
Code,
Descr,
CountryCode,
Price,
FromDate,
ToDate
FROM
[https://community.qlik.com/thread/219708]
(html, codepage is 1252, embedded labels, table is @4);
Concatenate
LOAD * INLINE [
Category, Code, Descr, CountryCode, Price, FromDate, ToDate
REFERER, NEWSLETTER, Fee 2, NO, 10, 2016-01-01, 2016-01-16
REFERER, NEWSLETTER, Fee 3, NO, 11, 2016-01-01, 2016-01-15
REFERER, NEWSLETTER, Fee 4, NO, 12, 2016-01-15, 2016-01-30
REFERER, NEWSLETTER, Fee 5, NO, 13, 2016-01-16, 2016-01-30
];
TMP:
NOCONCATENATE
LOAD * RESIDENT Order;
INNER JOIN
LOAD
//Category,
Code as Referer,
Code,
Descr,
CountryCode,
Price,
FromDate,
ToDate,
IF (CountryCode = 'SE', 'SEK',
IF (CountryCode = 'DE', 'EUR', 'Unknown')) as Currency
Resident Markup
WHERE Category = 'REFERER';
Concatenate (InvoiceLine)
LOAD
Applymap('OrderInvoiceKeyMap', OrderId) as InvoiceId,
Code,
Descr,
Price,
Currency
RESIDENT TMP
WHERE OrderDate >= FromDate and OrderDate < ToDate;
DROP TABLE TMP;
DROP table Markup;