topic Re: Exists Function Issue in QlikView

Exists Function Issue

Wed, 01 Jul 2015 06:47:42 GMT

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

Re: Exists Function Issue

MayilVahanan — Wed, 01 Jul 2015 07:02:00 GMT

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.

Re: Exists Function Issue

Wed, 01 Jul 2015 07:10:56 GMT

Hi ,

Later we are joining the TodayStockData with TSMVMT.

Re: Exists Function Issue

sunilkumarqv — Wed, 01 Jul 2015 07:12:23 GMT

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);

Re: Exists Function Issue

sasiparupudi1 — Wed, 01 Jul 2015 14:24:33 GMT

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;

Re: Exists Function Issue

pratap6699 — Wed, 01 Jul 2015 16:12:36 GMT

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