Add / concatenate data to table

[flygstolen_fred]

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;

[Anonymous · Accepted solution]

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;