15 Replies Latest reply: Feb 17, 2017 11:17 AM by Jack Hertel

# Apply value to every row in table

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.

CustomerQuarterSales
Customer1267
Customer1352
Customer1423

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.

• ###### Re: Apply value to every row in table

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

• ###### Re: Apply value to every row in table

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...

• ###### Re: Apply value to every row in table

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

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

• ###### Re: Apply value to every row in table

Try like this?

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

• ###### Re: Apply value to every row in table

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

• ###### Re: Apply value to every row in table

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.

• ###### Re: Apply value to every row in table

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

• ###### Re: Apply value to every row in table

I guess, technically both.

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

• ###### Re: Apply value to every row in table

How do you identify which quarter you want if missing?

• ###### Re: Apply value to every row in table

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

• ###### Re: Apply value to every row in table

Is there any date field coming in that excel sheet?

• ###### Re: Apply value to every row in table

Hi Crystle,

If you do:

TMP_TABLE:

RESIDENT FACT_TABLE;

LEFT JOIN (TMP_TABLE)

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

• ###### Re: Apply value to every row in table

This one is easier to do with a Cartesian join.

//100 Names with a few having sales

t1:

Name, Sales

Aaliyah Barr

Aaron Roac, 70

Abagail Serran, 300

Abbey Stei, 100

Abbigail Gallego, 1000

Abby Villega

......

Barrett Edward

];

Quarters:

Quarter

Q1

Q2

Q3

Q4

];

//Get the unique list of names

Sales:

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)

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)

Sales/4

Resident t1;

• ###### Re: Apply value to every row in table

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

• ###### Re: Apply value to every row in table

here you go.

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

Enjoy,

Jack