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

[mazacini]

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)
LOAD Pd,
     '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)
LOAD
     'A' as Item,
     'Z' as Ctgy,
     Num(1) as Rate
     Resident Trans

But the values stay at ‘-‘

Any ideas ?

[rwunderlich · Accepted solution]

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

[Sokkorn]

Hi Mazacini,

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

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

Do let me know if this one can help you.

Regards,

Sokkorn

[mazacini]

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?

Again, thanks for your help.

Joe

[rwunderlich]

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

-Rob