4 Replies Latest reply: Oct 16, 2018 7:17 AM by Beck Bakytbek RSS

    compare columns within a table

    Beck Bakytbek

      Hi Folks,

       

      i got a following Situation, my data does look like:

       

      type_of_action, comp1, comp2, comp3, comp4

      action1,                   x          x          x          x

      action2                    x          x                      x

      action3                    x                    x

      action4                    x                                   x

       

      my expected Output is to create the additional column with the feature like this

       

      type_of_action, comp1, comp2, comp3, comp4    Control

      action1,                   x          x          x          x                4

      action2                    x          x                      x                3

      action3                    x                    x                              2

      action4                    x                                   x               2

       

      does anybody have any ideas, how to resolve this issue?

       

      Thanks a lot

      Beck

        • Re: compare columns within a table
          Petter Skjolden

          You simply have this expression as a measure you can label Control in a table that has type_of_action as a dimension and the comp1 to comp4 as separate measures:

           

          =Count(comp1)+Count(comp2)+Count(comp3)+Count(comp4)

            • Re: compare columns within a table
              Beck Bakytbek

              Hi Peter,

               

              first of all, thanks a lot for your Feedback, at the beginning i solved this ussue by using of this Expression (like your suggestion), but then i thought, can i create from such Expression a Dimension (like 4, 3 , 2 ,1). Do you have any idea, how to create the columns or Dimension ( like 4 ,3, 2, 1) ?

                • Re: compare columns within a table
                  Petter Skjolden

                  You can use the CROSSTABLE prefix of the LOAD statement to unpivot your data to create a dimension that tracks the "comp". Then you could use the Pivot Table or the Table in the UI to calculate the Control as a normal aggregation.

                   

                  This would be a better data model for BI and analytics purposes.

                   

                   

                  CrossTable( comp , value ) LOAD

                    type_of_action,

                    comp1 AS [1],

                    comp2 AS [2],

                    comp3 AS [3],

                    comp4 AS [4]

                  FROM

                     ..... ;

                   

                   

                  In av Pivot Table you would use type_of_action and comp as dimensions and put the comp as a column not as a row. Finally you would create this expression as a measure:

                   

                  Count( value )

                   

                  In av regular/straight table you would have to add the type_of_action as a dimension but add the comp for each type manually as expressions like this:

                   

                  =Count( {<comp={1}>} comp )

                  =Count( {<comp={2}>} comp )

                  =Count( {<comp={3}>} comp )

                  =Count( {<comp={4}>} comp )

                   

                  And then finally:

                   

                  =Count( comp )

                   

                  for the Control