6 Replies Latest reply: Apr 23, 2018 10:41 AM by Francisco Pedrosa

# Creating Dimensions for Categories

Hello,

I have a table which contains data for several transactions. I need to create a dimension which can categorize each transaction based on specific columns but i'm not able to do it.

The Table as the following:

T:

[PL_PLAYER_ID, GM_GAME_NR, GM_MULTIPLA, GM_NR_BET_EVENTS, GM_GAME_TYPE,  GM_TOT_BET

];

Basically, i need to create a category where for each transaction having:

GM_MULTIPLA = 'S' and GM_NR_BET_EVENTS > 3 and GM_GAME_TYPE='ADSUB'

We will have:

if GM_TOT_BET < 1  ==> CATEG = '01SP < 1€'

if GM_TOT_BET < 2.5  ==> CATEG = '02SP < 2,5€'

if GM_TOT_BET < 2.5  ==> CATEG = '02SP < 5€'

Else CATEG = 'Other'

I've tried the following but it gives me Null.

= Aggr(

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 1, '01SPM < 1€',

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 2.5 , '02SPM < 2.5€',

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 5 , '03SPM < 5€',

,'OTHER'))), GM_GAME_NR, PL_PLAYER_ID)

Does anyone knows how to solve it ?

All help is appreciated.

Many Thanks,

Francisco

• ###### Re: Creating Dimensions for Categories

I am not sure I am able to follow your problem... would you be able to provide a sample or raw data with the expected output you are looking to get from it?

Best,

Sunny

• ###### Re: Creating Dimensions for Categories

Hi Sunny,

The idea is for example.

T:

[PL_PLAYER_ID, GM_GAME_NR, GM_MULTIPLA, GM_NR_BET_EVENTS, GM_GAME_TYPE,  GM_TOT_BET

1000, 100, S, 4, ADSUB, 1,

1010, 101, N, 1, ADSUB, 5,

1020, 102, S, 5, ADSUB, 2

1030, 103, S, 2, ADSUB, 1

1040, 104, S, 4, ADSUB, 4

1050, 105, S, 6, ADSUB, 100,

1060, 106, N, 1, MQSUB, 10

];

And basically, i would like to have a Dimension which will give me the GM_GAME_NR per category. For this example, it would be:

CATEGORY          GM_GAME_NR

-----------------------------------------------

'01SP < 1€'              NULL

'02SP < 2.5€'           100

'02SP < 2.5€'           102

'02SP < 5€'              104

'OTHER'                   101

'OTHER'                   103

'OTHER'                   105

'OTHER'                   106

If you need further clarification, please tell me.

Francisco

• ###### Re: Creating Dimensions for Categories

Try using Interval Match. look for the examples on comunity. or else the same can be done using if else condition if you have less number of categories.

To use aggr function, you need to have grouping functions like sum, avg etc... tp get the right result. in your case, there is no need to use aggr function as youre just comparing with the bets. if you want to compare with the total amount of bet, then you need to calculate the sum of the bet amount first and then apply if else condition or interval match.

Note: you cannot perform aggr on direct load. you need to first load data and then perform aggr on resident load.

• ###### Re: Creating Dimensions for Categories

Something like this?

• ###### Re: Creating Dimensions for Categories

Or just do it in the script

T:

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 1, '01SPM < 1€',

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 2.5 , '02SPM < 2.5€',

If(GM_GAME_TYPE='ADSUB' and GM_MULTIPLA='S' and GM_NR_BET_EVENTS > 3 and  GM_TOT_BET <= 5 , '03SPM < 5€','OTHER'))) as Category;

PL_PLAYER_ID, GM_GAME_NR, GM_MULTIPLA, GM_NR_BET_EVENTS, GM_GAME_TYPE, GM_TOT_BET

1000, 100, S, 4, ADSUB, 1

1010, 101, N, 1, ADSUB, 5

1020, 102, S, 5, ADSUB, 2

1030, 103, S, 2, ADSUB, 1

1040, 104, S, 4, ADSUB, 4

1050, 105, S, 6, ADSUB, 100

1060, 106, N, 1, MQSUB, 10

];

• ###### Re: Creating Dimensions for Categories

Thanks for the tip. It worked fine.