4 Replies Latest reply: Jun 11, 2012 10:24 AM by Rob Wunderlich

# Load: Add a constant as a value in Field1 based on value of Field 2

I have a Trans table:

 Month InvNo Item Ctgy Val Jan 1 A XY 10 Jan 2 B W 20 Jan 3 A XY 15 Jan 4 C W 12 Jan 5 A Z 20 Feb 6 A XY 15 Feb 7 A Z 17 Feb 8 B W 18 Feb 9 C W 11 Feb 10 A XY 14

I have a Rate table:

 Month Rate Jan 0.7 Feb 0.8

I use this code to load the rate field into the Trans table,

LEFT Join (Trans)
'A'
as Item,
'XY'
as Ctgy,

Rate
Resident

EDIT: This should read Resident Rate

 Month InvNo Item Ctgy Val Rate Jan 1 A XY 10 0.7 Jan 2 B W 20 - Jan 3 A XY 15 0.7 Jan 4 C W 12 - Jan 5 A Z 20 - Feb 6 A XY 15 0.8 Feb 7 A Z 17 - Feb 8 B W 18 - Feb 9 C W 11 - Feb 10 A XY 14 0.8

This gives me:

NOW, I WANT to Load the Constant 1 as the Rate for any Item A, which has a Ctgy of ‘Z’.

Ie I want this

 Month InvNo Item Ctgy Val Rate Jan 1 A XY 10 0.7 Jan 2 B W 20 - Jan 3 A XY 15 0.7 Jan 4 C W 12 - Jan 5 A Z 20 1.0 Feb 6 A XY 15 0.8 Feb 7 A Z 17 1.0 Feb 8 B W 18 - Feb 9 C W 11 - Feb 10 A XY 14 0.8

I thought   this might work:

LEFT Join (Trans)
'A'
as Item,
'Z'
as Ctgy,

Num(1) as Rate
Resident Trans

But the values stay at ‘-‘

Any ideas ?

• ###### Load: Add a constant as a value in Field1 based on value of Field 2

The second join doesn't work becaise the Rate field was created for all rows in the first Join -- and will not match the value for Rate in the second join. The solution is to collect in a temp table all the values and rows you want to join. Use multiple loads and concatenate as required. Then Join that temp table to the Trans table in a single Join.

-Rob

http://robwunderlich.com

• ###### Load: Add a constant as a value in Field1 based on value of Field 2

Hi Rob

Thanks for your help in resolving this.

Can you clarify something for me?

I thought my first join joined values per period ONLY FOR ITEM ='A' AND CTGY = 'XY'.

In fact, it joins those values, BUT ALSO CREATES A '-' FOR ALL OTHER VALUES OF ITEM AND CTGY.

Is that right?

Joe

• ###### Load: Add a constant as a value in Field1 based on value of Field 2

That is correct. Null values will be created for the added fields in all non-matching rows of the join.

-Rob

• ###### Re: Load: Add a constant as a value in Field1 based on value of Field 2

Hi Mazacini,

Not sure I understood you. But let try this solution:

```[Rate]:
'A' & Month,
Rate
SELECT * FROM TableRate;

[Tran]:
Month,
InvNo,
Item,
Ctgy,
Val,
IF(Ctgy='Z',1,APPLYMAP('Rate',Item & Month,''))    AS Rate
SELECT * FROM TableTran;
```