Okay, let´s take your code as example:
STEP 1: You should first do an aisolated first STORE so you have the qvd to retrieve the data later on.
You should name this table LOAD, so you can refer to it when you store it or use a Resident .
TABLE1:
LOAD
"Document Date",
Plant,
"Name of Supplier",
"Purchasing Group",
"Order Quantity",
"Purchasing Document",
Item,
Material,
"Short Text",
"Scheduled Quantity",
"Net Price",
"Order Price Unit",
Currency,
"Delivery date",
"Storage location",
"Issued Quantity",
"Qty Delivered",
"Quantity Received",
ApplyMap('REGIONMAP', Plant,'Rest of the world') As Region,
today() as [load date]
FROM [lib://DataFiles/Past Due delivery.XLSX]
(ooxml, embedded labels, table is Sheet1);
Imagine that TABLE1 is your final table and want to do the autoincremental save with this data each time you load it. Now you should only STORE this TABLE1 into a qvd for the first time, so you at least, create the qvd (container) with some data, because later you will use this same qvd as the "storage". So you do the STORE:
// Because I don´t know where you will save it in your computer and how you do it (I use a variable for the route/path) I will put xx:
// Historical Container is the name I decided to put, you can rename it obv, but don´t forget the .qvd and (qvd)
STORE TABLE1 into [lib://xx/xx/xxxx/Historical Container.qvd](qvd);
Now that you have your TABLE1 in a qvd container, you should be able to see it where you stored it, and will be the base for our auto incremental load.
STEP 2: Now, as you have the Historical Container.qvd, we will have to LOAD it, and then, CONCATENATE LOAD the same TABLE1, so you add it up to the rest of the data, that in theory, will have the same dimensions, but different data in it (the date, if you do it different days, or the data in general, if you are updating the Past Due delivery.XLSX frequently).
This Load will go FIRST, ONCE you have done the first and only STORE to create the qvd.
TABLE_HISTORIC:
LOAD
"Document Date",
Plant,
"Name of Supplier",
"Purchasing Group",
"Order Quantity",
"Purchasing Document",
Item,
Material,
"Short Text",
"Scheduled Quantity",
"Net Price",
"Order Price Unit",
Currency,
"Delivery date",
"Storage location",
"Issued Quantity",
"Qty Delivered",
"Quantity Received",
Region, // Because it´s already saved in the dimension Region, we just have to name the dimension, not all the applymap
"load date" // same as region, load date already has the data from the formula today()
// We get the qvd from the same path you have saved it before, unless you have changed placed where it was, then use the new path.
FROM [lib://xx/xx/xxxx/Historical Container.qvd](qvd);
Now we use the Concatenate with the first code you used, as you are loading new data:
Concatenate LOAD
"Document Date",
Plant,
"Name of Supplier",
"Purchasing Group",
"Order Quantity",
"Purchasing Document",
Item,
Material,
"Short Text",
"Scheduled Quantity",
"Net Price",
"Order Price Unit",
Currency,
"Delivery date",
"Storage location",
"Issued Quantity",
"Qty Delivered",
"Quantity Received",
ApplyMap('REGIONMAP', Plant,'Rest of the world') As Region,
today() as [load date]
FROM [lib://DataFiles/Past Due delivery.XLSX]
(ooxml, embedded labels, table is Sheet1);
STEP 3: Now, with this Load and Concatenate Load, your table TABLE_HISTORIC has the old data (First Load from this table) and the new data (Concatenate Load). If you visualize it, it should show all this data I´m saying. At last, this table with all the old and new data is Loaded, but you don´t store it anywhere right now, so STORE it in the same qvd you decided at the start:
STORE TABLE_HISTORIC into [lib://xx/xx/xxxx/Historical Container.qvd](qvd);
AS I SAID, Step 1 should only be executed once and only, I usally do it first, just the store, and delete the code (althought you will use this same code later in the proccess, but in the last places).
SO, if you have done STEP 1 and now want to do STEP2 AND STEP3, the whole code would be this:
TABLE_HISTORIC:
LOAD
"Document Date",
Plant,
"Name of Supplier",
"Purchasing Group",
"Order Quantity",
"Purchasing Document",
Item,
Material,
"Short Text",
"Scheduled Quantity",
"Net Price",
"Order Price Unit",
Currency,
"Delivery date",
"Storage location",
"Issued Quantity",
"Qty Delivered",
"Quantity Received",
Region, // Because it´s already saved in the dimension Region, we just have to name the dimension, not all the applymap
"load date" // same as region, load date already has the data from the formula today()
// We get the qvd from the same path you have saved it before, unless you have changed placed where it was, then use the new path.
FROM [lib://xx/xx/xxxx/Historical Container.qvd](qvd);
Concatenate LOAD
"Document Date",
Plant,
"Name of Supplier",
"Purchasing Group",
"Order Quantity",
"Purchasing Document",
Item,
Material,
"Short Text",
"Scheduled Quantity",
"Net Price",
"Order Price Unit",
Currency,
"Delivery date",
"Storage location",
"Issued Quantity",
"Qty Delivered",
"Quantity Received",
ApplyMap('REGIONMAP', Plant,'Rest of the world') As Region,
today() as [load date]
FROM [lib://DataFiles/Past Due delivery.XLSX]
(ooxml, embedded labels, table is Sheet1);
STORE TABLE_HISTORIC into [lib://xx/xx/xxxx/Historical Container.qvd](qvd);
NOTE: This code will always get the data, independent when you execute it, for example, you have loaded it twice the same day, if the second load the same day has different data from the first load, IT WILL REPLACE the data.
Sorry if it was too much!
Best regards
