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

    Creating Dimensions for Categories

    Francisco Pedrosa

      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


       

        • Re: Creating Dimensions for Categories
          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

            • Re: Creating Dimensions for Categories
              Francisco Pedrosa

              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

                • Re: Creating Dimensions for Categories
                  Naveen Chintala

                  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

                    Something like this?

                     

                    Capture.PNG

                      • Re: Creating Dimensions for Categories
                        Sunny Talwar

                        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

                        ];