topic Re: Catpuring exceptions while loding into fact table in QlikView

Catpuring exceptions while loding into fact table

Fri, 26 Jul 2013 04:07:48 GMT

Hi,

While loading the data into the fact table if there any dimension keys are missing/'not matching' how to capture those in an exception table at one go ??

Let's say I have 7 dimension tables to be looked at before loading into fact table , during the course of lookup in case of any non matching keys how do I capture it in an exception table ?? Using NOT EXISTS we can capture but exists() accepts only 2 parameters ??

Thanks

Sathya

Re: Catpuring exceptions while loding into fact table

Gysbert_Wassenaar — Fri, 26 Jul 2013 09:22:46 GMT

You can specify more than one condition in the where clause:

Load * from somewhere

where not exists(DimA) and not exists(DimB) and not ...etc

Re: Catpuring exceptions while loding into fact table

Fri, 26 Jul 2013 10:40:16 GMT

Thanks Gysbert..

Re: Catpuring exceptions while loding into fact table

Fri, 26 Jul 2013 10:53:46 GMT

I was bit hasty... Here is the requirement. From the below, the custid 15 which is in Excep table is not there in Customer table and Productid 16 is there in Excep table which is not there in Products table.

These 2 records to be captured and loaded . Any suggestions pll.. If I use AND it'/s not w orking

Customer:

Load * Inline [

CustID,Custname,

10,'Chris'

11,'Tom',

];

Products:

Load * Inline [

ProdID,ProductName,

10,'Samsung Galaxy',

11,'Samsung GTS-5253',

15,'Samsung GTS-55S3'

];

NoConcatenate

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,15,10,

4,16,15,

]

WHERE NOT EXISTS(ProdID,ProductID) AND NOT Exists(CustID,CustomerID);

store Excep into a:\qvd.qvd

Re: Catpuring exceptions while loding into fact table

Fri, 26 Jul 2013 10:59:48 GMT

there is a typo in the Excep table

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,16,10,

4,16,15,

]

WHERE NOT EXISTS(ProdID,ProductID) AND NOT Exists(CustID,CustomerID);

store Excep into a:\qvd.qvd

Re: Catpuring exceptions while loding into fact table

Gysbert_Wassenaar — Fri, 26 Jul 2013 11:25:29 GMT

Oh, you want OR instead of AND if any of the conditions is enough to put it in the Excep table.

NoConcatenate

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,16,10,

4,16,15,

4,15,18,

4,19,10,

]

WHERE NOT EXISTS(ProdID,ProductID) OR NOT Exists(CustID,CustomerID);

See attached example.

Re: Catpuring exceptions while loding into fact table

Fri, 26 Jul 2013 11:42:01 GMT

2 records to be picked up but none of them picked up

Excep:

Load * Inline [

InvID,ProductID,CustomerID

1,10,10

2,11,10

3,16,10 - Product id 16 is not there in the Products table

4,15,12 - Customer id 12 is not there in the customer table

]