14 Replies Latest reply: Feb 19, 2015 11:57 PM by Timanshu Virmani RSS

    How can we create a new field based on the values of some other fields??

    John Sumesh

      Can someone help me to resolve this issue?

       

      I have four columns Say

       

      Product 1_SalesProduct 2_SalesProduct 3_SalesProduct 4_Sales
      1001000
      045600
      001230
      000345

       

      Now the challenge is to create a new column field called Type with a Text,which is based on the <>0 Value in the first 4 columns.

       

      Product 1_SalesProduct 2_SalesProduct 3_SalesProduct 4_SalesType
      1001000Good
      045600Bad
      001230Average
      000345Better

       

      Please share your solutions???

        • Re: How can we create a new field based on the values of some other fields??
          Rupesh Bharadwaj

          cud you please explain a little more how you decide the Type i.e. good, bad

            • Re: How can we create a new field based on the values of some other fields??
              John Sumesh

              Thanks Rajan!!!

               

              My concern was,say I have 3k rows in my data.. Doesn't it requires the for loop to create the 'Text' ...plz dnt consider the literal meaning,the logic is,if the cell value of the column Product1_Sales <>0,then the corresponding cell in the column Type should populate the text 'Good' and if the cell value of the column Product2_Sales<>0,the corresponding cell in the Type column in the same row should populate the text 'Better',same applies to other columns....ultimately the column Type should contain the same number of rows as in the other columns with the respective string.

            • Re: How can we create a new field based on the values of some other fields??
              Massimo Grossi

              How to choose Good, Bad, ....?

              From your post Good is the max but Bad isn't the min.

               

               

              1.jpg

              a:

              load rowno() as id, * inline [

              Product1_Sales ,Product2_Sales ,Product3_Sales, Product4_Sales

              1001, 0, 0, 0

              0, 456 ,0, 0

              0 ,0 ,123, 0

              0 ,0, 0, 345          ];

               

              b:

              load id, rangesum(Product1_Sales ,Product2_Sales ,Product3_Sales, Product4_Sales) as AllProduct_Sales

              Resident a;

               

              left join (a)

              load id, pick(match(rowno(), 1, 2, 3, 4), 'Good', 'Better', 'Average', 'Bad') as Type

              Resident b

              order by AllProduct_Sales desc;

               

              DROP Table b;

                • Re: How can we create a new field based on the values of some other fields??
                  John Sumesh

                  Thanks Massimo!!!

                   

                  My concern was,say I have 3k rows in my data.. Doesn't it requires the for loop to create the 'Text' ...plz dnt consider the literal meaning,the logic is,if the cell value of the column Product1_Sales <>0,then the corresponding cell in the column Type should populate the text 'Good' and if the cell value of the column Product2_Sales<>0,the corresponding cell in the Type column in the same row should populate the text 'Better',same applies to other columns....ultimately the column Type should contain the same number of rows as in the other columns with the respective string.

                • Re: How can we create a new field based on the values of some other fields??
                  Sunny Talwar

                  Does this work?

                   

                  Table:

                  LOAD * INLINE [

                      Product 1 Sales, Product 2 Sales, Product 3 Sales, Product 4 Sales

                      1001, 0, 0, 0

                      0, 456, 0, 0

                      0, 0, 123, 0

                      0, 0, 0, 345

                  ];

                   

                  Table2:

                  LOAD *,

                    If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 1 Sales]), 'Good',

                    If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 2 Sales]), 'Bad',

                    If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 3 Sales]), 'Average',

                    If(Rangemax([Product 1 Sales], [Product 2 Sales], [Product 3 Sales], [Product 4 Sales]) = RangeMax([Product 4 Sales]), 'Better')))) as Type

                  Resident Table;

                   

                  DROP Table Table;

                   

                  Best,

                  S