Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

Hi,

For now it is resolved.

Inside pick() we should use Flag instead of RowNo().

Regards,

Sujeet

12 Replies
eruditio
Contributor III

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

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

Not applicable

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

Highlighted
Not applicable

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

eruditio
Contributor III

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

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

Not applicable

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

eruditio
Contributor III

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

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

Not applicable

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

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

MVP
MVP

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

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

eruditio
Contributor III

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

Very nice Jonathan