Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
help4qv123
Creator II
Creator II

Expression Help

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

9 Replies
sunny_talwar

Try this:

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


Capture.PNG

help4qv123
Creator II
Creator II
Author

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

sunny_talwar

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.)

help4qv123
Creator II
Creator II
Author

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)

sunny_talwar

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)

or

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

help4qv123
Creator II
Creator II
Author

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

sunny_talwar

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.

help4qv123
Creator II
Creator II
Author

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

sunny_talwar

What about this:

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