Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have table
table1:
LOAD * INLINE [
month,rep,sales,product
Jan ,101,3000,p1
Jan,102,2340,p1
Jan,103,2450,p2
Jan,103,1200,p1
Jan,101,1211,p2
Jan,103,1111,p2
feb,102,5000,p1
feb,103,4000,p2
feb,102,2000,p3
feb,103,6543,p1
March,101,2310,p1
March,103,1200,p2
];
Rep 101 does not sell anything on month of Feb.
I have a list of products
p1,p2,p3,p4,p5
I want to add the product p1,p2,p3,p4,p5 value 0 for feb for Rep 101 and
March rep 101 already has sold product p1 -but I want to add p2,p3,p4,p5 for sales 0 for March.
I have millions of records. Looping will kill my performance for comparing the products.
Please advice is there any better way to handle this situations.
We should verify the performance:
tab:
LOAD * INLINE [
month,rep,sales,product
Jan ,101,3000,p1
Jan,102,2340,p1
Jan,103,2450,p2
Jan,103,1200,p1
Jan,101,1211,p2
Jan,103,1111,p2
feb,102,5000,p1
feb,103,4000,p2
feb,102,2000,p3
feb,103,6543,p1
March,101,2310,p1
March,103,1200,p2
];
// a table with all months, all products, all reps
t:
load Distinct month Resident tab;
join load Distinct rep Resident tab;
join load Distinct product Resident tab;
Concatenate(tab)
load *, 0 as sales Resident t;
drop Table t;
Are you sure you need all these 0's? See how a pivot table may look like just with your sample data, zeroes are there:
This should work in a load script:
table1:
LOAD
AutoNumber( month & '|' & product & '|' & rep ) AS %Key,
*
INLINE [
month,rep,sales,product
Jan ,101,3000,p1
Jan,102,2340,p1
Jan,103,2450,p2
Jan,103,1200,p1
Jan,101,1211,p2
Jan,103,1111,p2
feb,102,5000,p1
feb,103,4000,p2
feb,102,2000,p3
feb,103,6543,p1
March,101,2310,p1
March,103,1200,p2
];
Products:
LOAD DISTINCT
product
RESIDENT
table1;
Months:
LOAD DISTINCT
month
RESIDENT
table1;
Reps:
LOAD DISTINCT
rep
RESIDENT
table1;
nProds = NoOfRows('Products');
nMonths = NoOfRows('Months');
nReps = NoOfRows('Reps');
LOAD
*
WHERE Not( Exists( %Key ));
LOAD
AutoNumber(month & '|' & product & '|' & Peek(rep,IterNo()-1,'Reps')) AS %Key,
month,
product,
Peek(rep,IterNo()-1,'Reps') AS rep,
0 AS sales
WHILE IterNo()<=$(nReps);
LOAD
month,
Peek(product,IterNo()-1,'Products') AS product
WHILE IterNo()<=$(nProds);
LOAD
Peek(month,RecNo()-1,'Months') AS month
AUTOGENERATE
$(nMonths);
// IF NOT NEEDED DROP THE HELPER TABLES...
DROP TABLES Products, Months, Reps;
One more version, rather simple script additions, no loops, to make dimension selections mutually independent, so you can see more 0's
that's how it should be done