Add records to table if Item reference does not exist in other table

[salto]

Hi QlikView community,

     First, Happy New Year to all of you!

     Now, I come up with my question: I have a SalesPrices table in which I have the pricelist for the items we are selling. The relevant fields are these:

     ItemRef, StartingDate, EndDate, PriceListName, Price

     In my load script, I would need to append new records to this SalesPrices table. Those records need to be all those ItemRef that do not have a PriceListName between StartingDate and EndDate.

     I have thought about selecting all distinct PriceListName values in a separate table (let us name it TariffNames). 

     For every ItemRef in SalesPrices, I need to check that every PriceListName in TariffNames exist in SalesPrices. For every non-existent PriceListName in SalesPrices, I need to add a new record in SalesPrices with:

     The ItemRef field,

     01/01/2013 as StartingDate,

     31/12/2013 as EndingDate,

     PriceListName (non-existing one)

     Price (zero value)

     How can I look for those non existing values and the TariffNames table, and append those records to SalesPrices table?

     Any help on this will be highly appreciated.

[Nicole-Smith]

Can you give some example data?  It doesn't have to be your real data.  It's just so I can get an idea of what your data looks like.

[#3 · Accepted solution]

Try Like below:

TempSalesPrices:

LOAD ItemRef, StartingDate, EndDate From Source_SalesPrice.qvd (qvd);

Join (TempSalesPrices)

LOAD DISTINCT PriceListName From Source_SalesPrice.qvd (qvd) ;

Left Join

LOAD ItemRef, StartingDate, EndDate , PriceListName , Price From Source_SalesPrice.qvd (qvd) ;

SalesPrices:

NoConcatenate

LOAD ItemRef, StartingDate, EndDate , PriceListName , Alt(Price ,0) AS Price

Resident TempSalesPrices ;

DROP Table TempSalesPrices ;

[salto]

Fantastic. Many many thanks!