1 Reply Latest reply: Feb 18, 2016 5:51 AM by Jonas Melo RSS

    how to group all the fields in to one(vertical column)

    naveen kumar

      Hello All

      I got struck with an requirement which i am able to achieve partially .the actual requirement data seem to be like this

      table1:

      location  score            quater    date(mm/dd/yyyy)

      mumbai      100            q1        03/12/2015

      mumbai      101            q2        06/20/2015

      mumbai      103              q3        09/05/2015

      pune          200              q1        03/12/2015

      pune          100            q2      06/20/2015

       

      the requirement was to achieve current score ,for this i wrote the expression by using firstsortvalue which look like this

      straight table:

      dimension :location

      expression :  FirstSortedValue(score,-date) ,FirstSortedValue(quater,-date)by this i can achieve current date max score which i got perfect, but now they have extended the requirement a level up

      Result:

      location        score            quater 

      mumbai          103                  q3

      pune              100                  q2  

      i achieved this task by writing the above expressions.

       

      Now the requirement is like this

      table2:(A SAMPLE DATA NOT ACCURATE)

      DATE  Quality Governance    Sampling    Lab Analysis    Failure Management    Supplier Management      RM Specifications  score

      03/12/2015                  20                          70                  100                150                          10            20                          100

      06/20/2015                   10                          10                  10                20                          10            10                            101

        09/05/2015                  30                          20                    20              30                          10            10                            103

      03/12/2015                  10                          20                  50                10                        20            10                            200 

      06/20/2015                    10                            40                20                  50                      40            10                            100

       

        So now the requirement is when i press 103(score field)  From the result table it should show be like this

       

      FINAL TABLE

      location      Quality Governance Sampling Lab Analysis  Failure Management  Supplier Management  RM Specifications  score 

      mumbai            30                          20                    20              30                                    10                          10              103       

      Pune                10                            40                20                  50                                  40                    10                    100

       

      Everything is predefine only my concern is how to dispaly them for my client ,i mean shall I display both the tables in same sheet or shall i wirte trigger so that after pressing 103 (SCORE FIELD) Result table it will bring me to Final table which is in sheet 02 ...

      is there any chance to group all these fields (Quality Governance Sampling Lab Analysis  Failure Management  Supplier Management  RM Specifications) in a single group and display everything in one table instead of two different tables in pivot

      like this

      final table:(pivot table)

      location       group                 score                 quater

      mumbai    +                              103                  q3

      pune         +                              100                  q2

       

       

      thanks

      naveen

        • Re: how to group all the fields in to one(vertical column)
          Jonas Melo

          Hi, naveen kumar.

           

          If you create a composite key in your data model would no solve your problem?  Ex.:

           

          Table1:

          Load

          autonumber(score& '_' & Date(date))   as [%Composite Key]

          location,

          score,

          quater

          from Table1;


          Table2:

          autonumber(score& '_' & Date(DATE))   as [%Composite Key]

          DATE,

          Quality,

          Governance,

          Sampling,

          [Lab Analysis],

          [Failure Management],

          [Supplier Management],

          [RM Specifications],

          score

          from Table2;

           

          Regards,

           

          Jonas Melo.