Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

salto
Valued Contributor II

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

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.

1 Solution

Accepted Solutions
Not applicable

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

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 ;


3 Replies

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

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.

Not applicable

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

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
Valued Contributor II

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

Fantastic. Many many thanks!

Community Browser