topic Re: Count expression in edit script generating an error in QlikView

Count expression in edit script generating an error

Fri, 26 Aug 2011 06:40:28 GMT

HI all,

When i am writting an Count expression in edit script , then it is generating an error with out any message just displaying the table.

Below line i am using , is there any mistake in this ?

if(DefectName ='Defect.EdgeChip',if(Count(DefectStatus='Fail')>0,2,1),'Null') as [Status EdgeChips]

Thanks in advance

Regards

Venkat

Count expression in edit script generating an error

kaushiknsolanki — Fri, 26 Aug 2011 06:47:37 GMT

Hi,

Count is a aggrigation function, so if you want to use it in script, you need to specify the group by function.

Regards,

Kaushik Solanki

Count expression in edit script generating an error

its_anandrjs — Fri, 26 Aug 2011 06:47:41 GMT

Hi,

As i understand your code that is to be written some thing like this will work better please check it

if(DefectName ='Defect.EdgeChip',if(DefectStatus='Fail',Count(DefectStatus)>0,2,1),'Null') as [Status EdgeChips]

Regards

Anand

Re: Count expression in edit script generating an error

its_anandrjs — Fri, 26 Aug 2011 06:56:12 GMT

Hi,

Yes Count is aggregation function as kaushik says you need to aggregate your code try this code

Count(if(DefectName ='Defect.EdgeChip',Count(if(DefectStatus='Fail' and Count(if(DefectStatus)>0,2,1))))) as [Status EdgeChips]

And let me know

Regards

Anand

Re: Count expression in edit script generating an error

Fri, 26 Aug 2011 07:18:03 GMT

Hi Kaushik,

My Task requirement is " if any one Fail is having in an LensKey then the Status should be 'FAIL' or 'Pass' " and also see the attached script document.

The below is the sample example.

Ex1

LensKey, DefectStatus

1, Fail

1,Pass

1,-

For this ex1 , the result should be = Fail

Ex2:

LensKey, DefectStatus

2, Pass

2,Pass

2,-

For this ex2 , the result should be = Pass

Ex3

LensKey, DefectStatus

3,Fail

For this ex3 , the result should be = Fail

Ex4

LensKey, DefectStatus

4,-

For this ex4 , the result should be = NotAvailable.

Thanks in advance

Regards

Venkat

Re: Count expression in edit script generating an error

Fri, 26 Aug 2011 08:20:40 GMT

Hi anandrjs,

My Task requirement is " if any one Fail is having in an LensKey then the Status should be 'FAIL' or 'Pass' " and also see the attached script document.

I Used below line in edit script, it is not getting the correct result . Can you suggest the correct solution to get this.

if(sum([Status EdgeChips1])>=3,'Fail',if(sum([Status EdgeChips1])>=1,'Pass','NA')) as [Status EdgeChips]

The below is the sample example.

Ex1

LensKey, DefectStatus

1, Fail

1,Pass

1,-

For this ex1 , the result should be = Fail

Ex2:

LensKey, DefectStatus

2, Pass

2,Pass

2,-

For this ex2 , the result should be = Pass

Ex3

LensKey, DefectStatus

3,Fail

For this ex3 , the result should be = Fail

Ex4

LensKey, DefectStatus

4,-

For this ex4 , the result should be = NotAvailable.

Thanks in advance

Regards

Venkat

Count expression in edit script generating an error

kaushiknsolanki — Fri, 26 Aug 2011 09:23:13 GMT

Hi,

I havent tried this as i am not using my laptop now, but you can give a try.

Data:
Load * inline [
Student , Status
1, Fail
1, Pass
1, NA
2, Fail
2, Fail
2, Fail
3, Pass
3, Pass
3, Pass
4, NA
4, NA
4, NA
];

Data1:
load
student,
count(if(status = 'Fail'),status) as status_F_count
count(if(status = 'Pass'),status) as status_P_count,
count(if(status = 'NA'),status) as status_NA_count,
resident Data;

Data2:
Load
Student,
if(status_f_count >= 1,'Fail',
if(Status_P_Count >=1, 'Pass','NA')) as Final_Status
Resident Data1;

Drop table Data1;

Regards,

Kaushik Solanki

Re: Count expression in edit script generating an error

Fri, 26 Aug 2011 09:59:47 GMT

Hi Kaushik,

When i am using your code in edit script it show an syntax error line in below lines

count(if(status = 'Fail'),status) as status_F_count

count(if(status = 'Pass'),status) as status_P_count,

count(if(status = 'NA'),status) as status_NA_count,

resident Data;

Regards

Venkat

Re: Count expression in edit script generating an error

its_anandrjs — Fri, 26 Aug 2011 10:05:44 GMT

Hi,

Now check this you left some commas on that.

Data1:
load
student,
count(if(status = 'Fail'),status) as status_F_count,
count(if(status = 'Pass'),status) as status_P_count,
count(if(status = 'NA'),status) as status_NA_count
resident Data;

Data2:
Load
Student,
if(status_f_count >= 1,'Fail',
if(Status_P_Count >=1, 'Pass','NA')) as Final_Status
Resident Data1;

Drop table Data1;

Regards,

Anand

Re: Count expression in edit script generating an error

kaushiknsolanki — Fri, 26 Aug 2011 10:07:55 GMT

Hi,

Yes i am sorry actually we need so use the group by function.

Try this.

Data:
Load * inline [
Student , Status
1, Fail
1, Pass
1, NA
2, Fail
2, Fail
2, Fail
3, Pass
3, Pass
3, Pass
4, NA
4, NA
4, NA
];

Data1:
load
student,
count(if(status = 'Fail'),status) as status_F_count
count(if(status = 'Pass'),status) as status_P_count,
count(if(status = 'NA'),status) as status_NA_count,
resident Data group by student;

drop table Data;

Data2:
Load
Student,
if(status_F_count >= 1,'Fail',
if(Status_P_Count >=1, 'Pass','NA')) as Final_Status
Resident Data1;

Drop table Data1;

EDIT:- Have a look at the attached document.

Regards,

Kaushik Solanki