topic Re: Handling the rejections while loading the data. in QlikView

Handling the rejections while loading the data.

Mon, 12 Aug 2013 06:27:55 GMT

Finding this bit tricky.. Can someone pl help on this.

I need to capture the missing ProductID and the customerIDs while loading the data into SalesFact. Need to load the data only when the CustID and ProductID are there in the customer & Product tables. Also, I need to reject those custID, ProductID which are not there in the customer & Product tables

to an error table ... This is just an example, in my case, I need to lookup almost 6 tables (Product, customer etc.,) and perform rejection

Customer:

Load * Inline [

CustID,Custname,Address

10,'Subash',Indra Nagar

11,'Ashok',Chromepet ];

SalesFact:

Load * Inline [

InvID,ProductID,CustomerID,SalesrepID,ShipperID,Sales,Margin

1,10,10,AA1,SH1,25800,40

2,11,10,AA1,SH1,15800,40

3,16,10,AA1,SH2,25800,40

4,15,12,AA1,SH2,25800,40 ];

Product:

Load * Inline

[

ProductID,ProdName,Price

10,P1,100

12,P2,150

];

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 06:39:48 GMT

I think the easiest way would be to join the three tables with a left join:

SalesFact_Aux:

load*

Resident SalesFact;

Drop table SalesFact;

left join (SalesFact_Aux)

load*

Resident Customer;

drop table Customer;

left join (Sales_Aux)

load*

Resident Product;

drop table Product;

Once you have everything in one table, you could create two tables from here. One with the sales that do have a existing customer and product:

Sales:

Load*

Resident SalesFact_Aux

where not(isNull(Custname) or isNull(ProdName));

And other table with the errors (just the opposite where clause):

Sales_Error:

Load*

Resident SalesFact_Aux

where (isNull(Custname) or isNull(ProdName));

Hope it helps

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 07:59:05 GMT

Thanks Losu. Can't we do left join on a resident table ...

Am getting an error "Table not found (SalesFact_Aux)

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 08:03:37 GMT

In the example I wrote, I created a resident table called SalesFact_Aux (just for making it more clear). Watch out not to skip that step:

SalesFact_Aux:

NoConcatenate LOAD*

Resident SalesFact;

Drop table SalesFact;

You could of course do the left joins directly to your SalesFact table..

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 08:10:02 GMT

Thanks Losu , its working fine as expected.

I need to have these set of errored records to populated in my SalesFact table as well by having 'unknown' value populated for those missing records

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 08:16:09 GMT

Ok. If you want the errored values also in the SalesFact table, you should reload the SalesFact_Aux table and mark the missing values:

SalesFact:

Noconcatenate LOAD*,

If(IsNull(Custname) or IsNull(ProdName), 'Unknown') as isMissingValue

Resident SalesFact_Aux;

Drop Table SalesFact_Aux;

That way you will have all the sales and you will be able to select only the errored ones or the correct ones. Works for you that way?

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 08:28:44 GMT

there are 2 issues

1. Now all the records are getting loaded in the Salefact table but its getting duplicated.

ProductID

2. If(Isnull(ProductID),'Missing',ProductID) --- It puts all the Productid as 0

Pl help

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 08:52:57 GMT

See the attached example. Is that what you want?

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 09:12:06 GMT

Sorry Losu.. I couldn't open the qvw's created by others.

Appreciate if you could copy/paste the code here..

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 09:16:15 GMT

// First step, we join the tables:
Left Join (SalesFact)
LOAD*
Resident Product;
DROP Table Product;
Left Join (SalesFact)
LOAD*
Resident Customer;
DROP Table Customer;
// Here we create a new field to mark the errored values:
Left Join (SalesFact)
LOAD
    InvID,
    if(IsNull(ProdName) or IsNull(Custname), 'unknown', 'correct') as isMissingValue
Resident SalesFact;

Then you use set analysis to select correct or incorrect invoices:

sum({<isMissingValue={'unknown'}>}Sales)

The result:

Re: Handling the rejections while loading the data.

Mon, 12 Aug 2013 09:26:07 GMT

Thanks Losu...Its working fine now