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


        • 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:



            • 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


                    comp1 AS [1],

                    comp2 AS [2],

                    comp3 AS [3],

                    comp4 AS [4]


                     ..... ;



                  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