Replace value if another table value exists?

Fri, 26 Jul 2013 19:43:47 GMT

FactTable:

ID	Amount
1	25
2	30

DimTable:

In the script, I want to replace Amount with 0 if any of its types are B, else leave it alone, resulting in:

NewTable:

ID	Amount
1	0
2	30

How can I do this?

Re: Replace value if another table value exists?

MayilVahanan — Fri, 26 Jul 2013 20:25:30 GMT

Try like this

FactTable:

Load * Inline

[

ID,Amount

1,25

2,30

];

DimTable:

Load * Inline

[

ID,Type

1,A

1,B

2,A

2,C

];

Inner Join(FactTable)

Load ID,Concat(Type,',') as Type Resident DimTable Group by ID;

Test:

NoConcatenate

Load ID,Type, If(WildMatch(Type,'*B*'), 0, Amount) as Amount Resident FactTable;

DROP Tables DimTable, FactTable;

PFA