Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ;
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.
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 ;
Fantastic. Many many thanks!