topic Re: Expression Help in QlikView

Expression Help

help4qv123 — Mon, 09 May 2016 12:05:08 GMT

Hi All,

Plz Find the attached QVW....

I want to fetch the count of ID which dont have COL1 value and whose COL2 value is 'Raw'...

ie.., Here the count is 3.....

By using the above expression Count({1-$<COl1={'*'}>}DISTINCT ID) gives me 3(count for ID whose COL1 value is null.....

I want to include Col2='Raw'...result should be 2....

=Count({$<Col2 ={'Raw'}, COl1-={'*'}>} ID)...This is giving me 0...

I want these two expressions to be merged

1) Count({$<Col2 ={'Raw'}>} ID)

2) Count({1-$<COl1={'*'}>}DISTINCT ID)

Thanks

Re: Expression Help

sunny_talwar — Mon, 09 May 2016 12:11:26 GMT

Try this:

=Count({$<Col2 ={'Raw'}, ID = e({<COl1={'*'}>})>} ID)

Re: Expression Help

help4qv123 — Mon, 09 May 2016 12:45:28 GMT

Hi Sunny,

This is working with excel..when i pull data from database the same is not working. it is giving zero..

Any other alternative

Re: Expression Help

sunny_talwar — Mon, 09 May 2016 12:48:18 GMT

It would be difficult to say what could be the reason. Can you see any difference in the way the data is structured in your actual application vs. the sample? Also have you made sure to not make any error when you posted the expression (errors such as Field upper or lower case, missing curly brackets etc.)

Re: Expression Help

help4qv123 — Mon, 09 May 2016 12:53:17 GMT

Yes sunny expression i exact the same. But it gives me 0 when i use exclude or use negative operator..this works in excel.

=Count({$<ID-={''}>}DISTINCT ID)

Re: Expression Help

sunny_talwar — Mon, 09 May 2016 12:56:41 GMT

Is your COL1 truly null or can there be white space issue? Can you try this:

=Count({$<Col2 ={'Raw'}, ID = e({<COl1={"=Len(Trim(COl1)) > 0"}>})>} ID)

=Count({$<Col2 ={'Raw'}, ID = {"=Len(Trim(COl1)) = 0"}>} ID)

Re: Expression Help

help4qv123 — Mon, 09 May 2016 13:00:03 GMT

Actually Col1 does not have any null or blank in my database...i want to count those ID's which do not have any value in col1. There are few Id's who donot have any col1 values

Re: Expression Help

sunny_talwar — Mon, 09 May 2016 13:03:14 GMT

When you say they do not have any value, it could either mean the value is null or missing. The above expression should work in case of nulls.

Re: Expression Help

help4qv123 — Mon, 09 May 2016 13:04:43 GMT

These expression gives the result. But when selection of any field it gives 0.

Re: Expression Help

sunny_talwar — Mon, 09 May 2016 13:34:34 GMT

What about this:

=Count({$<Col2 ={'Raw'}, ID = e({<COl1={"=Len(Trim(Only({1}COl1))) > 0"}>})>} ID)