Need help on the segregating the data on the below scenario
Hi Team,
I have attached a sample data with expected output data. The requirement is very simple however I couldn't produce the expected result. i have 3 columns , age band, Comments and Trans Id. Based on the comments available in need to find the count of Trans Id. like,if any comment is available against the Trans Id then I have to count that id and show the column as "With Comment". For this I am using the formula as If(len(Trim([Comment]))>0, Count([Trans ID])) and if NULL or Blank is there in the comment column against the Trans ID then I need to consider the column as "Without Comment" and for this I am using the formula as If(len(Trim([Comment]))=0, Count([Trans ID])). when I used this expression "If(len(Trim([Comment]))>0, Count([Trans ID]))" it is showing null as a result(Attached Document as present output) for the with comment column. whereas with the expression If(len(Trim([Comment]))=0, Count([Trans ID])), it showing all the counts including with comment and without comment. I need to segregate the data based on the age band and with comment and without comment as attached the excel sheet for reference. Please help me on this.
When you use the function IF() outside of SUM() or COUNT(), the IF condition is being evaluated "globally", with the full scope of available data. You want these conditions to be evaluated for each row of data. For that, you need to place your IF functions inside of the aggregation function - something like this:
count(distinct IF(len(trim(Comment)) > 0, ID))
This syntax, however, works very slowly with large data sets. It's a lot better to use Set Analysis for these cases. A sample syntax could look like this:
There is a lot of terminology and syntax to unpack here, if you are not familiar with Set Analysis. Let me invite you to my session on Set Analysis at the Masters Summit for Qlik, which is coming to Hamburg, Germany this September.
When you use the function IF() outside of SUM() or COUNT(), the IF condition is being evaluated "globally", with the full scope of available data. You want these conditions to be evaluated for each row of data. For that, you need to place your IF functions inside of the aggregation function - something like this:
count(distinct IF(len(trim(Comment)) > 0, ID))
This syntax, however, works very slowly with large data sets. It's a lot better to use Set Analysis for these cases. A sample syntax could look like this:
There is a lot of terminology and syntax to unpack here, if you are not familiar with Set Analysis. Let me invite you to my session on Set Analysis at the Masters Summit for Qlik, which is coming to Hamburg, Germany this September.
Thank you so much for you quick reply with logic behind. It works perfectly as expected. I will implement the same by using the set analysis expression. Thanks once again.
