topic Create Aggregation filtering from different table in App Development

Create Aggregation filtering from different table

ArmandP — Thu, 12 Jan 2023 17:25:53 GMT

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!

Re: Create Aggregation filtering from different table

vinieme12 — Fri, 13 Jan 2023 07:07:56 GMT

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;

Re: Create Aggregation filtering from different table

ArmandP — Fri, 27 Jan 2023 09:29:08 GMT

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;