topic Re: How to change expression in straight table? in QlikView

How to change expression in straight table?

Mon, 09 Jun 2014 11:12:37 GMT

Hi,

I have data in excel as below

Date ID outcome share process

23/03/2014 4242 Failed Fail Fail

I created a straight table to show no of fail wit the below expression

count({<Result={'Failed'}>}Distinct ID)

but in the table it is not showing the ID why because in excel there are two instances as Failed and Fail. Can anyone suggest me please how to change the above expression to include both Failed and Fail. I tried like below but it is not working

count({<Result={'Failed','Fail'}>}Distinct ID)

Thanks.

Re: How to change expression in straight table?

senpradip007 — Mon, 09 Jun 2014 11:15:01 GMT

Where is your Result field as you have explained inyour original post?

Re: How to change expression in straight table?

swuehl — Mon, 09 Jun 2014 11:15:12 GMT

This should work if 'Result' is your field and not 'outcome'.

You can also try a search in your set expression:

count({<Result={"Fail*"}>}Distinct ID)

Re: How to change expression in straight table?

Gysbert_Wassenaar — Mon, 09 Jun 2014 11:17:37 GMT

I don't see a field called Result. Perhaps you mean outcome instead: count({<outcome={'Fail*'}>}Distinct ID)

Re: How to change expression in straight table?

Mon, 09 Jun 2014 11:26:12 GMT

Thanks. The Result is from cross table. actually I used a cross table

TblData:

CrossTable(Test,Result,3)

LOAD

Date

ID ,

Outcome,

[share],

[Process]

FROM

[Report.xlsx]

(ooxml, embedded labels, table is Sheet);

Re: How to change expression in straight table?

Mon, 09 Jun 2014 11:28:14 GMT

Thanks. tried with outcome but it is showing incorrect values. Do I need to change anything?

Re: How to change expression in straight table?

senpradip007 — Mon, 09 Jun 2014 11:28:22 GMT

Can you upload sample qvw along with xlsx file?

Re: How to change expression in straight table?

Mon, 09 Jun 2014 13:18:20 GMT

Since you made a cross table, your "Test" column I think will contain "Share" and "Process", and then Result will contain "Fail" or "Failed". So maybe try:

count({<Test = {'Share'}, Result={'Fail*'}>}Distinct ID)

count({<Test = {'Process'}, Result={'Fail*'}>}Distinct ID)

Re: How to change expression in straight table?

sarvjeet — Tue, 10 Jun 2014 00:55:11 GMT

I recommend handle this in scripts. Create a bit flag "flg_failed" with value '0' and '1' using apply-map .

1 for failed or fail

0 for anything else

Expression in Chart should be : sum(flg_failed)

Aggregation is faster then Count or set-analysis

-Sarvjeet

Re: How to change expression in straight table?

Anonymous — Tue, 10 Jun 2014 01:17:27 GMT

Below expression should work,

Count ({<Result={"*Fail*"}>} DISTINCT ID)

You can even align "Fail", "Failed" etc to unique data "Fail" in backend script and use below expression.

Count ({<Result={"Fail"}>} DISTINCT ID)