Handling the rejections while loading the data.

[#1]

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

];

[#2]

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

[#3]

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

Am getting an error "Table not found (SalesFact_Aux)

[#4]

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..

[#5]

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

[#6]

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?

[#7]

there are 2 issues

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

    ProductID

     10

     10

     11

    11

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

Pl help

[#8]

See the attached example. Is that what you want?

[#9]

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

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

[#10 · Accepted solution]

// 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: