topic Re: Creating Dimensions for Categories in App Development

Creating Dimensions for Categories

Anonymous — Mon, 23 Apr 2018 10:40:18 GMT

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:

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

Re: Creating Dimensions for Categories

sunny_talwar — Mon, 23 Apr 2018 11:14:57 GMT

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

Anonymous — Mon, 23 Apr 2018 13:42:23 GMT

Hi Sunny,

Thanks for your time.

The idea is for example.

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

Re: Creating Dimensions for Categories

naveenkumarchin — Mon, 23 Apr 2018 13:48:26 GMT

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

sunny_talwar — Mon, 23 Apr 2018 13:58:24 GMT

Something like this?

Re: Creating Dimensions for Categories

sunny_talwar — Mon, 23 Apr 2018 13:59:28 GMT

Or just do it in the script

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

];

Re: Creating Dimensions for Categories

Anonymous — Mon, 23 Apr 2018 14:41:05 GMT

Thanks for the tip. It worked fine.