Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

flygstolen_fred
New Contributor III

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;

1 Solution

Accepted Solutions
gompa786
Contributor III

Re: Add / concatenate data to table

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;

10 Replies
gompa786
Contributor III

Re: Add / concatenate data to table

Hi Fredrick,

Once you have loaded the data from the Mark up table you can drop it as you have already concatenated the data and to get the invoice id for the current order you can do a look up on the invoice table by doing a join on the Invoiceid as it seems to be the key field..

Thanks

Chandra

flygstolen_fred
New Contributor III

Re: Add / concatenate data to table

Thanks for trying to help out Chandra.

Could you point me in the right direction where to put the join?

gompa786
Contributor III

Re: Add / concatenate data to table

Hi Fredrik,

Please find the code I have written to serve your purpose.. this concatenates your InvoiceLines table:


Markup_Temp:
LOAD
    Code,
    Descr,
    CountryCode,
    Price
FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
(biff, embedded labels, table is Markup$);

join(Markup_Temp)
LOAD
    OrderId,
    CountryCode
FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
(biff, embedded labels, table is Order$);

join(Markup_Temp)
LOAD
    InvoiceId,
    OrderId
FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
(biff, embedded labels, table is Invoice$);


[InvoiceLines$]:
Load 0 as [InvoiceLineId],
           InvoiceId,
           [Code],
        [Descr],
        [Price] Resident Markup_Temp;
          
           concatenate
LOAD [InvoiceLineId],
[InvoiceId],
[Code],
[Descr],
[Price]
FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
(biff, embedded labels, table is InvoiceLines$);

Drop Table Markup_Temp;

Note: Please mark correct or helpful so that other members can refer in future

Thanks

CHandra

flygstolen_fred
New Contributor III

Re: Add / concatenate data to table

This is my LOAD statement, where do I add the join between Order and Invoice table to lookup the InvoiceId in the for-loop?

Order:

LOAD

     OrderId,

     CountryCode;

Invoice:

LOAD

     InvoiceId,

     OrderId;

InvoiceLines:

LOAD

     InvoiceLineId,

     InvoiceId,

     Code,

     Descr,

     Price;

Markup:

LOAD

    Code,

    Descr,

    Price,

    CountryCode;

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;

flygstolen_fred
New Contributor III

Re: Add / concatenate data to table

Hi,

Thanks for trying to help.

Do I need to change the order what tables to load first?

Se my LOAD script below, do I need to move the Markup LOAD to the top?

Why do I need to JOIN Markup_Temp after the LOAD script?

Qlik Sense connects all data/fields correctly between tables except the markup table.

gompa786
Contributor III

Re: Add / concatenate data to table

Hi,

What I have done is created a temp table to be dropped later called Markup_Temp  and loaded all the other 3 table s by joining on the order id to get the Invoice id into my temp table and concatenated to the Base Fact InvoiceLines Table and dropped the markup Temp table ... and I checked the result it appended to the Invoicelines table without any problem... I have created your temp data in my excel so that's what you see in the from for the tables...

Thanks

Chandra

flygstolen_fred
New Contributor III

Re: Add / concatenate data to table

Is it possible to change to order because I have all the load scripts in different tabs in the "data import view".

I need to be able to "comment out" the markup-part sometimes when I load new data.

Is this the best order to load data?

The markup table contains approx 100 rows and the Order table contains more than 250 000 rows.

Tab1:

Order:

LOAD

     OrderId,

     CountryCode;

Invoice:

LOAD

     InvoiceId,

     OrderId;

InvoiceLines:

LOAD

     InvoiceLineId,

     InvoiceId,

     Code,

     Descr,

     Price;

Tab2:

Markup:

LOAD

    Code,

    Descr,

    Price,

    CountryCode;

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;

gompa786
Contributor III

Re: Add / concatenate data to table

The order of loading the table doesn't matter I have given you the logic of how to achieve the concatenation to the InvoiceLines table. The order table , Invoice table are just look up tables and mark up table is the table which has the fresh data or new data so ultimately you want all the data from mark up table and the invoice id from the invoice table which will be achieved by looking up to that table with the Order id .. So wherever (tabs) you load the data that's fine but only ensure you get all the info in one temp table before concatenating and drop the temp table..  

flygstolen_fred
New Contributor III

Re: Add / concatenate data to table

Thanks for trying to help out.

I can't get it to work by adding join(markup) before the order and invoice table.

Is maybe the Lookup or ApplyMap a better solution?

Is it possible to add Lookup inside the for-loop to get the InvoiceId?

Is this possible at all?

LOOKUP('InvoiceId', 'OrderId', PEEK('OrderId',$(i), 'Order'), 'Invoice');