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.

[hector_munoz]

Hi Cristle,

A possible solution would be:

• 1st load the fact table as usual
• 2nd concatenate a set of data formed by all the combinations of Client+Quarter+Sales(0)
• 3nd group by Client+Quarter and sum Sales

In this way you will always have a record per Client and Quarter.

I hope it serves.

Regards,

H

[vishsaggi]

Can you share a sample if possible? May be use like

IF(Len(Trim(Yourfieldname)) = 0, 0) AS yourfieldname

[crystles]

The problem is, I don't have a table with all Customers+Quarters+Sales

I need to just add the Quarter if it's missing with a value of 0 for Sales.... if that's possible...

[vishsaggi]

Try like this?

IF(Len(Trim(Quarter)) = 0 , 0) AS Sales

[crystles]

There are no calculations for the fields. Just two tables.

One table has all the Customers.

The other table has a sales number that's pulled from an Excel document with the Quarter that Sale was in.

But the problem is, there aren't values for every Quarter for every Customer in the excel document... that's why I need to add a value for each Customer, per Quarter.

[crystles]

But if the Quarter doesn't exist, it's not going to give me a value for the sales?

[vishsaggi]

You want to add the value for your sales field or in Quarter field ?

[crystles]

I guess, technically both.

If the Quarter isn't in the Excel document, I still need to have a value for that Quarter.

[hector_munoz]

Hi Crystle,

If you do:

TMP_TABLE:
LOAD DISTINCT _KEY

RESIDENT FACT_TABLE;

LEFT JOIN (TMP_TABLE)

LOAD DISTINCT QUARTER,

                              0     AS SALES

RESIDENT FACT_TABLE;

, you will have all the key+quarter combinations and a 0 as sales. Later you concatenate to the fact table and do the group by and sum.

Regards,
H