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]

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]

Thanks for trying to help out Chandra.

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

[Anonymous]

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]

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]

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.

[Anonymous]

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]

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;

[Anonymous]

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]

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');