Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
flygstolen_fred
Creator
Creator

Add / concatenate data to table

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:

OrderIdCountryCode
1234SE
1235NO

Invoice:

InvoiceIdOrderId
7891234
7901234
7911235

InvoiceLines:

InvoiceLineIdInvoiceIdCodeDescrPrice
45645789CPCDescription 1150
45871789CPODescription 2100
46987790CPKDescription 380
54679791CKJDescription 4105

Markup:

CodeDescrCountryCodePrice
HKJExtra productSE17
JKBExtra productNO15

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;

10 Replies
Anonymous
Not applicable

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;