Need Help in Scripting

[Anonymous]

Hi Expert

I have product list with 63 items (fixed). and customer list is 150 (fixed)

and  i have trans table as below.Each customer would not get transaction for all the product items (that means 63) each month. In trans table i have to add the remaining(missing) product items for each customer though transaction made or not.

if i make my master table [150 customer * 63 product items] and then right join with trans table  i would achieve the result. But it is increasing my master rows. There is any way only with product table i can reaming items in trans table for each customer

Please help me how can we achieve it script

TransTable

Month	Product	Customer	Amt
April	P001	C001	5
April	P002	C001	1
April	P003	C001	1
April	P004	C001	1
April	P001	C002	1
April	P002	C002	1
April	P004	C002	1

Regards

Ramu

[tresesco]

You can probably use left join with master table instead.

[Anil_Babu_Samineni]

You can try with left join

Main:

Load * from source;

Left join(main)

Load *, fields from master calendar ;

[effinty2112]

Hi Ramu,

                    What about something like:

Temp:

Load

Product,

Customer,

0 as Zero,

'April' as Month

Resident Master;

Left Join(Temp)

Load * Resident TransTable;

TransTable1:

Load

Month,

Product,

Customer,

RangeSum(Amt, Zero) as Amt

Resident Temp;

Good luck

Andrew

[maxgro]

test data (products, customers)

Products:

// P001 to P006

load 'P00' & rowno() as Product AutoGenerate 6;

Customers:

// C001 to C005

load 'C00' & rowno() as Customer AutoGenerate 5;

// your data, I added one row, may

TransTable:

load * inline [

Month, Product, Customer, Amt

April, P001, C001, 5

April, P002, C001, 1

April, P003, C001, 1

April, P004, C001, 1

April, P001, C002, 1

April, P002, C002, 1

April, P004, C002, 1

May, P004, C002, 1

];

// all months x products x customers

Final:

LOAD Distinct Month Resident TransTable;

join (Final) LOAD Product Resident Products;

join (Final) LOAD Customer Resident Customers;

// add trans table amount

left Join (Final) load * Resident TransTable;

DROP Table TransTable;