Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
How do you identify which quarter you want if missing?
That's the problem.... I don't know either?
Is there any date field coming in that excel sheet?
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;
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
here you go.
Drop all of the junk table to get rid of the synthetic keys.
Enjoy,
Jack