Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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