Creating Dimensions for Categories

[Anonymous]

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:

Load * inline

[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

[sunny_talwar]

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

[Anonymous]

Hi Sunny,

Thanks for your time.

The idea is for example.

T:

Load * inline

[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.

I appreciate your help.

Thanks in advance,

Francisco

[naveenkumarchin]

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.

[sunny_talwar]

Something like this?

[sunny_talwar · Accepted solution]

Or just do it in the script

T:

LOAD *,

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;

LOAD * INLINE [

    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

];

[Anonymous]

Thanks for the tip. It worked fine.