12 Replies Latest reply: Jun 17, 2014 8:05 AM by Sujeet Padhi RSS

    How to add Different Expressions for Different rows of a table ???

      Hi All,

       

      I have to design a table, in which each row will be filled up with different expressions using set analysis.

      I am using LOAD * INLINE to create the table.

       

      LOAD * INLINE [

          Services, Parameters, Value

          Alert Support, % Acknowledged, count({$<"Alert Support Ack SLA" = {'Met'}>}"Alert Support ID")/count("Alert Support ID")

          Alert Support, % Resolved ,count("Alert Support ID")  

      ];

       

      The Expressions for Value field are different for different rows.

       

      Is it possible to create this type of table, which will have different expressions for different rows.

      Can someone please suggest what needs to be done ??

       

      Please help ...

      Thanks in advance

       

      Regards

      Sujeet

        • Re: How to add Different Expressions for Different rows of a table ???
          Antoine Frangieh

          Hi Sujeet,

           

           

          I think you could do this.

           

          I assume that each row, the evaluation criteria would be Services and Parameters.

          Start by tagging each row to understand what type of service it is and what parameters it has:

           

          Assuming that only 2 combinations are possible, you will get 2 possible:

          Flag 1: Sercices = Alert Support ; Parameters =  % Acknowleged

          Flag 2: Sercices = Alert Support ; Parameters =  % Resolved

          (You could of course extend the logic)

           

          YourDataTableTemp:

          LOAD

               *,

                Flag

          From yoursourcetable,

           

          Map_Flags:

          MAPPING

          LOAD Flag, Value INLINE [

              Flag, Value

              1, count({$<"Alert Support Ack SLA" = {'Met'}>}"Alert Support ID")/count("Alert Support ID")

              2,count("Alert Support ID")  

          ];

           

           

          Then apply the map within your table

          Table:

          LOAD

               *,

               ApplyMap('Map_Flags', Flag) as YourRowExpression

          Resident YourDataTableTemp;

          DROP TABLE YourDataTableTemp;

           

          Hope it helps,

          Antoine

            • Re: How to add Different Expressions for Different rows of a table ???

              Hi Antoine,

               

              Below is my code.

               

              Summary :

              LOAD * INLINE [

               

               

                  Services, Parameters, Flag

                  Alert Support, % Acknowledged , 1

                  Alert Support, % Resolved , 2

                  Alert Support, % Resolved in L1/L2/L3 , 3

                  Incident Management, % Escalated , 4

                  Incident Management, % Resolved , 5

                  Problem Management, % Investigated , 6

                  Problem Management, % Closed(FULLY RESOLVED ??) , 7

                  Maintenance, % Schedule Deviation , 8

                  Maintenance, # UAT defects (OR track unittest case?) , 9

                  Maintenance, # backed-out deployments , 10

                  Maintenance, # prod remigrations , 11

                  Maintenance, # Incidents caused , 12

                  User Support, % Acknowledged , 13

                  User Support, % Resolved , 14

                  User Support, % Resolved in L1/L2/L3 , 15

                 

              ];

               

               

              Map_Flags:

              MAPPING

              LOAD Flag, Value INLINE [

                  Flag, Value

                  1, count({$<"Alert Support Ack SLA" = {'Met'}>}"Alert Support ID")/count("Alert Support ID")

                  2,count("Alert Support ID") 

              ];

               

               

              Table:

              LOAD

                   *,

                   ApplyMap('Map_Flags', Flag) as YourRowExpression

              Resident Summary;

              DROP TABLE Summary;

               

               

              I tried this.

              But the expressions are not getting calculated, they are just getting printed as strings.

              I used a straight table to display the values.

              Can you please suggest what changes I have to make ???

                • Re: How to add Different Expressions for Different rows of a table ???

                  Hi Antoine,

                   

                  Just For your Information.

                  In the straight table I used the expression as =$(= YourRowExpression) to calculation.

                  Whenever I use only one expression or One type of expression for all the rows, it works fine.

                  But If I use different expressions for different rows then it is not working.

                   

                  Also tried with table box. But in table box the value of the "YourRowExpression" field is not getting calculated, It only prints it as a string.

                   

                  Regards,

                  Sujeet

                    • Re: How to add Different Expressions for Different rows of a table ???
                      Antoine Frangieh

                      Sujeet,

                       

                      Using the Set Analysis won't work in the code.

                      It can only be used within your chart, that you have figured out.

                       

                      It would be helpful for me if you could put a sample data so that I could help out.

                       

                      Cheers,

                      Antoine

                        • Re: How to add Different Expressions for Different rows of a table ???

                          Hi Antoine,

                           

                          Below are my source table contents.

                           

                          Alert Support ID             Rota            Severity     Alert_Type          Received_Time              Alert Support Ack SLA

                          ------------------            ----------------------------------------------------------------------------------------------------------------------------------------

                          SR28727648                 REPDB          High         Latency             6/5/2014 10:59:31 PM      Breached

                          SR28727647                 REPDB          High         Latency             6/5/2014 10:58:22 PM      Met

                          SR28727615                 REPDB          High         Latency             6/5/2014 10:49:53 PM      Met

                          SR28727553                 REPDB          High         PET                   6/5/2014 10:34:08 PM     Met

                          SR28727612                 REPDB          High         Latency             6/5/2014 10:48:19 PM      Met

                          SR28727614                 REPDB          High         Latency             6/5/2014 10:49:27 PM      Breached

                          SR28727678                 PRODUCTS    High        JOBFAILURE     6/5/2014 11:01:51 PM      Met

                          SR28727701                 REPDB          High         Latency             6/5/2014 11:08:27 PM      Met

                           

                          Is it possible to specify different expressions for different rows in a straight table or pivot table ?

                          I am not able to do that. It gives proper result only when the expression is the same for all the rows.

                           

                          If possible, please reply me soon.

                          It is very urgent.

                           

                          Regards,

                          Sujeet

                            • Re: How to add Different Expressions for Different rows of a table ???
                              Antoine Frangieh

                              OK.
                              Back to this:

                              Summary :

                              LOAD * INLINE [

                               

                               

                                  Services, Parameters, Flag

                                  Alert Support, % Acknowledged , 1

                                  Alert Support, % Resolved , 2

                                  Alert Support, % Resolved in L1/L2/L3 , 3

                                  Incident Management, % Escalated , 4

                                  Incident Management, % Resolved , 5

                                  Problem Management, % Investigated , 6

                                  Problem Management, % Closed(FULLY RESOLVED ??) , 7

                                  Maintenance, % Schedule Deviation , 8

                                  Maintenance, # UAT defects (OR track unittest case?) , 9

                                  Maintenance, # backed-out deployments , 10

                                  Maintenance, # prod remigrations , 11

                                  Maintenance, # Incidents caused , 12

                                  User Support, % Acknowledged , 13

                                  User Support, % Resolved , 14

                                  User Support, % Resolved in L1/L2/L3 , 15

                                 

                              ];

                               

                              Then wihin your Straight table, create an expression as follows:

                              IF (Flag = 1, count({$<"Alert Support Ack SLA" = {'Met'}>}"Alert Support ID")/count("Alert Support ID"), IF (Flag = 2 , count("Alert Support ID")   ......

                               

                              It is sub-optimal, I'll think of a more elegant solution. It will solve the urgency

                          • Re: Re: How to add Different Expressions for Different rows of a table ???
                            Jonathan Dienst

                            Hi

                             

                            First off, you cannot use expressions in a table box.

                             

                            The reason that you see that behaviour with a straight table, is that the dimension(s) you are using in the straight table are not correctly associating with the YourRowExpression field, so you have multiple possible values for your expression.

                             

                            To do what you want to do, you will need to build a Pick(RowNo()) expression in your load script. See the attached.

                             

                            HTH

                            Jonathan

                      • Re: How to add Different Expressions for Different rows of a table ???

                        Hi,

                         

                        Some more information.

                         

                        I tried this one. this is working but not fully.When I display the values Flag and Value (mExpr) in the straight table then it shows fine. But if I add other colums Services and Parameters then it is not working.It shows a common value for all the rows in the Value(mExpr) field.
                        I am attaching screen shot.
                        Also If I choose one of the values from the table then it is not showing the proper value.
                        Pic1.PNG.pngpic2.PNG.pngpic3.PNG.pngCan you please suggest what can be done ?