Create Aggregation filtering from different table

[ArmandP]

Hello,

I have two tables like the ones on the top and I would like to create an Aggregation/Bucket like the one in the bottom. Any idea how to reach that?

I was trying to create a dimension and going row by row. Something like:

IF(
     MATCH([Field_1],Field) AND
     MATCH([Field_2], Spot_On_Filters.Value),
     [BKT],
     'Not Mapped'
)

Can't find a solution tough. Any help is welcome. Thank you!

[vinieme12]

Use ApplyMap() to map the BKT to the FACT table

MapBKT:

Mapping Load Value,BKT

From FilterTable;

Load Field_1

,Field2

,Value

,Applymap('MapBKT',Field_1,'Not Mapped') as BKT

From Fact;

[ArmandP]

I can't get it to work.

Here I leave the code I've used:

NOCONCATENATE
[FACT_TABLE]:
LOAD *
INLINE
[
Field_1,Field_2,Value
Value1,ONE,1
Value_2,TWO,2
Value_3,THREE,3
Value_4,FOUR,4
Value_5,FIVE,5
](delimiter is ',');


NOCONCATENATE
[FILTER_TABLE]:
LOAD *
INLINE
[
BKT,Field,Value
ONE,Field_1,Value_1
ONE,Field_2,ONE
TWO,Field_1,Value_2
TWO,Field_2,TWO
THREE,Field_1,Value_5
](delimiter is ',');

MapBKT:
Mapping Load Value,BKT
From FILTER_TABLE;

Load Field_1
,Field2
,Value
,Applymap('MapBKT',Field_1,'Not Mapped') as BKT
From FACT_TABLE;