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

[#1]

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

[Anonymous]

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

[#3]

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 ???

[#4]

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

[Anonymous]

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

[#6]

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

[Anonymous]

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

[#8]

Hi,

Ok I will try this one and will get back to you asap.

But, please tell me if there is any optimal way to do this.

It will be very helpful.

Regards,

Sujeet

[jonathandienst]

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

[Anonymous]

Very nice Jonathan