Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
burak_ersoy
Contributor III
Contributor III

Set Analysis to filter out 3 digit zipcodes

Hello All,

We wrote a set analysis script in order to filter out the patients with 3 digit zip codes in our dataset. Here is the script:

Count({$<PAT_ZIP-={"???"}>}PAT_ID)

It was working just fine in version 3.2; however, it stopped working without an error after our system update to Feb 2018.

Do you have any suggestions to fix this script for version Feb 2018?

Thanks in advance.

Sincerely,

Burak

1 Solution

Accepted Solutions
wdchristensen
Specialist
Specialist

I am assuming you are trying to exclude US provinces and territories outside the United States States (Virgin Islands / Ruerto Rico). These locations start with 2 leading zeros and could be the source of your issue. I suspect the cause of this issue is the dropping of leading zeros in your zip code. Likely  version 3.2 data type was acting like an integer and dropping the leading zeros and now it is acting like a string. If this theory is correct then adding two leading zeros back in should "Fix" your set analysis.

Count({$<PAT_ZIP-={"00???"}>} PAT_ID)


However you might want to consider why you are excluding these records in the first place. I know an answer was already marked corrected but please mark this answer as helpful if my answer was also "correct". Good luck!


View solution in original post

15 Replies
sunny_talwar

See if this works

Count({$<PAT_ZIP = {"=Len(PAT_ZIP) <>  3"}>} PAT_ID)

burak_ersoy
Contributor III
Contributor III
Author

Hello Sunny Talwar,

Unfortunately, it didn't do the trick. Any other suggestions?

Thanks,

Burak

sunny_talwar

Would you be able to share an image where your and/or mine expression is used?

vishsaggi
Champion III
Champion III

May be he can try your trick like in the script he can create a flag

LOAD Col1,

           PAT_ID,

           PAT_ZIP,

           IF(Len(Trim(PAT_ZIP)) <> 3, 1, 0) AS ZipFlag

From YourSource;

= Count({$< ZipFlag = {1} >} PAT_ID)

sasiparupudi1
Master III
Master III

May be Try

Count({$<PAT_ID-={'=Len(TRIM(PAT_ZIP))=3'}>}PAT_ID)

burak_ersoy
Contributor III
Contributor III
Author

I am utilizing both Qlik Sense 3.2 and Qlik Sense Feb 2018. The script is just a KPI measure. And it is the only script in my measure expression. Hope it helps and I am sorry that I am not able to share a screenshot due to confidentiality.

sunny_talwar

I know there has been change in the Quoteology, but what you have been using should not have been impacted... look here for changes

Quotes in Set Analysis

burak_ersoy
Contributor III
Contributor III
Author

Dear Sasidhar Parupudi and Sunny Talwar,

Both of your responses make sense syntactically and logically. I tried them in both environments (QS 3.2 and QS Feb 2018). Both scripts are working correctly in QS 3.2. However, it does not work in QS Feb 2018. I heard some issues in set analysis in Feb 2018 update. I will try Vishwarath Nagaraju's data load level recommendation and post the results in here as soon as I can.

As my last resort, I am planning to convert the set analysis expressions to basic if statements. I hate to do that workaround, but it is what it is.

I am sure it will take awhile to test all things above. Therefore, I will appreciate all other suggestions in the mean time.

Sincerely,

Burak

burak_ersoy
Contributor III
Contributor III
Author

Sunny Talwar,

Thank you for sharing. I read the Qlik Design Blog post before and I agree. It shouldn't effect the results in the scripts that we discussed. However, it is not working properly in QS Feb 2018. Please see my first response below to Sasidhar's recommendation.

I am still open for suggestions.

Thanks,

Burak Ersoy