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

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

    Josetxo 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.