Replace value if another table value exists?

[#1]

FactTable:

ID	Amount
1	25
2	30

DimTable:

ID	Type
1	A
1	B
2	A
2	C

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?

[MayilVahanan]

Hi

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