Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
// 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:
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
Thanks Losu. Can't we do left join on a resident table ...
Am getting an error "Table not found (SalesFact_Aux)
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..
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
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?
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
See the attached example. Is that what you want?
Sorry Losu.. I couldn't open the qvw's created by others.
Appreciate if you could copy/paste the code here..
// 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: