Exists Function Issue

[#1]

Hello All,

I have an issue here.

We have a table TodayStockData with records for 3 stores a, b and c. There is one more table TSMVMT which gets the data from database. This runs daily.

On a particular day, new store data say d is coming into the TSMVMT. Now on that data TodayStockData table does not have the record for store d as it is first time it has come in. In this case we want to create a record in TodayStockData for that new store with zeros in the respective sales and stock. Later proceed with the calculation.

TodayStockData:

NoConcatenate

Load OrgID,

        ProdID,

       OrgID&ProdID as tOrgProdKey,

        StockQty

Resident StockData;

TSMVMT:

Load OrgID,

        ProdID,

        OrgID&ProdID as rOrgProdKey,

        StockQty

From DATABASE;

TodayStockData:

Load Distinct OrgID,

                    ProdID,

                    rOrgProdKey as tOrgProdKey,

                    0 as StockQty

Resident TSMVMT

where not exists (tOrgProdKey, rOrgProdKey);

We are getting duplicate records when we are using this.

How can we achieve this requirement?

Thank you.

Regards,

Pramod K

[MayilVahanan]

Hi

Your script is looks fine.

Are you doing any join later that?

Try like this

Let vNo = NoOfRows('TodayStockData');

TRACE $(vNo);

In log file , check how many rows are fetching in this table.

[#3]

Hi ,

Later we are joining the TodayStockData with TSMVMT.

[sunilkumarqv]

Check this

TodayStockData:

NoConcatenate

Load OrgID,

        ProdID,

       OrgID&ProdID as tOrgProdKey,

        StockQty

Resident StockData;

TSMVMT:

Load OrgID,

        ProdID,

        OrgID&ProdID as rOrgProdKey,

        StockQty

From DATABASE;

TodayStockData:

Load Distinct OrgID,

                    ProdID,

                    rOrgProdKey as tOrgProdKey,

                    0 as StockQty

Resident TSMVMT

where not exists (rOrgProdKey , rOrgProdKey);

[sasiparupudi1]

Hi

The reason for this is because you are getting synthetic keys because of orgid and Prodid..I suggest you to rename those fields and you should get your desired result

StockData:

Load

OrgID&ProdID as tOrgProdKey,

OrgID as stockDataID,ProdID as StockDataProdID ,StockQty as StockQty

Inline

[

OrgID,ProdID,StockQty

1,1,100

2,2,100

3,3,300

];

TSMVMT:

Load

OrgID&ProdID as rOrgProdKey,

OrgID as TSMVMTOrgID,

ProdID AS TSMVMTProdID,StockQty as TSMVMTStockQty

Inline

[

OrgID,ProdID,StockQty

4,4,100

5,5,100

6,6,300

];

TodayStockData:

Load rOrgProdKey as tOrgProdKey,0 as StockQty

Resident TSMVMT

where not exists (tOrgProdKey, rOrgProdKey);

drop Table TSMVMT;

[pratap6699]

ya if another field occures in table,,,there is a syntetic key problem occures by using linktable with composite key eliminate it....