topic Re: Add records to table if Item reference does not exist in other table in QlikView

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

salto — Thu, 02 Jan 2014 11:50:51 GMT

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.

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

Nicole-Smith — Thu, 02 Jan 2014 15:05:33 GMT

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.

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

Thu, 02 Jan 2014 15:29:22 GMT

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 ;

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

salto — Fri, 03 Jan 2014 11:05:04 GMT

Fantastic. Many many thanks!