Incremental QVD

[maahivee]

Hello Guys,

I have a situation in my Application: We have open orders in a table which once invoiced will be moved to the invoice table.

but the order number is not same it is changed once it is invoiced.

In my situation I am trying to create the QVD's for entire application, I wrote the Incremental QVD (Insert Only) for the invoice table,

But I am confused which kind of Incremental QVD should I use for the open orders table?

I 1st thought of using the insert/update/delete type but then I was confused which column I am supposed to use as a Primary Key.

Also for the order number to use as a primary key, It is repeating, it is not set to like for only one row, because the order number may have multiple items and its each row for each item. Also i am not able to see any other column as a primary key, Since non have that functionality.

Can I still use Order Number a primary key even though it is repeating?  Will it really work and insert/update/delete the records without reading the whole table.

Can anyone please give me the scripts to do so?

Thank you so much.

[ramoncova06]

I would assume serial number is your lowest granular attribute, in that case you can create a composite key for the for the sale order and serial number, that will give you a unique entry to use the incremental logic

order:

load

serial&'_'&saleorder as %K_Order,

fields ....

from your source;

concatenate(order)

load

%K_Order,

fields ....

from you incremental qvd

where not exists(%K_Order,%K_Order);

[maahivee]

Hi Ramon,

Your script seems so helpful, very thankful for that.

but the problem there no such column as serial or sequence number. But i do have item number field which when used with order number creates a kind of unique key. can i use it.

[Anonymous]

Insert/update/delete type is ok, you can use just Order number as primary key, it is repeating, yes, but you won't use it to join tables but just to identify the new ones and a order number won't appear again if it already was used in the past, the only problem is that you are saying it changes when it's delivered, do you get all the data in a single table with invoices and orders together?

[maahivee]

Hello Oscar,

NO the open orders table is different and invoice is diff table once order is completed they invoice it, and it moves into invoice table with its order number changed.

but again say if we have 10 items under one order that order has 10 rows in the table but if we ship only 7 items that 7 rows are deleted from the open orders table and moved to invoice table and the remaining 3 still sits in the open orders table until they are shipped with that same order number.

also what do you mean by joining tables?

[Anonymous · Accepted solution]

By joining I mean linking, normally, in a database you have linked (joined) tables in your schema, a dummy example would be the example below, where you are linking the orders table with the customers table. That's when you need a unique primary key to avoid duplicated results.

Orders                  Customers
----------                  ----------------

OrderID            |--  CustomerID

ItemID             |       Name

CustomerID --- |      Telephone

Anyway, after your explanation if partial invoices occur, then yes, concatenate the order number & item number if both are numeric just do

Ordernumber&Itemnumber as Primarykey

if both or one is alphanumeric then use

autonumber(Ordernumber&Itemnumber) as Primarykey

autonumber converts text to a unique numeric value so it works faster in QV.

[maahivee]

Oscar- Thank you very much. this is really very very helpful.

but can you please also tell me the script for incremental qvd in this case.

[Anonymous]

Hello, take a look to this page, it has all you need there. Regards!

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

[maahivee]

Thank you so much Oscar.