Product not exists - add manual values zero

[#1]

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.

[Clever_Anjos]

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;

[Anonymous]

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:

[petter]

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;

[Anonymous]

One more version, rather simple script additions, no loops, to make dimension selections mutually independent, so you can see more 0's

[petter]

that's how it should be done

[Anonymous]