Apply value to every row in table

[crystles]

I have master list of customers. I then have a table that is linked to this table by a key field with a Fiscal Quarter and a Sales field.

The problem is, that the second table doesn't have a value for the Sales for each Quarter, for each customer. I need Sales to have a value and not be null in this table for each Quarter, but I'm not sure how to accomplish this.

So say I have 100 customers. Then I have 4 Quarters for the year.

I should have 400 records. Each customer should have a value, to show for each Quarter, for the Sales, even if that value is 0.

Customer	Quarter	Sales
Customer1	1 (instead of null)	0    (instead of null)
Customer1	2	67
Customer1	3	52
Customer1	4	23

I have tried just creating a table with the master list and having a 0 for the Sales field and then Concatenating that to the second table but I don't think that is giving me the results I want.

Any help would be greatly appreciated.

[vishsaggi]

How do you identify which quarter you want if missing?

[crystles]

That's the problem....  I don't know either?

[vishsaggi]

Is there any date field coming in that excel sheet?

[jackhertel]

This one is easier to do with a Cartesian join.

//100 Names with a few having sales

t1:

LOAD * INLINE [

    Name, Sales

    Aaliyah Barr

    Aaron Roac, 70

    Abagail Serran, 300

    Abbey Stei, 100

    Abbigail Gallego, 1000

    Abby Villega

......

    Barrett Edward

];

Quarters:

LOAD * INLINE [

    Quarter

    Q1

    Q2

    Q3

    Q4

];

//Get the unique list of names

Sales:

Load Distinct Name 

Resident t1;

//Use a cartesian product to create 400 rows (100 * 4) bu left joining the Quarters table with no linking field This makes your 400 rows

Left Join (Sales)

Load Quarter

Resident Quarters;

//Add in the sales I spread it out 1/4 to each quarter.  The ones with no sales get a zero

Left Join (Sales)

Load  Name,

      Sales/4

Resident t1;

[jackhertel]

Sorry, you wanted zeros not nulls.  Just add a column to the quarter in line load for nosales with a 0 in it. left join that and add it to sales to get sales with a zero.  I will update app

[jackhertel]

here you go.

Drop all of the junk table to get rid of the synthetic keys.

Enjoy,

Jack