3 Replies Latest reply: Jan 3, 2014 6:05 AM by Josetxo Amonarriz RSS

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

    Josetxo Amonarriz

      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.