Solved: Re: Set Analysis to filter out 3 digit zipcodes - Qlik Community

burak_ersoy · ‎2018-04-03

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

wdchristensen · ‎2018-04-03

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

sunny_talwar · ‎2018-04-03

See if this works

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

burak_ersoy · ‎2018-04-03

Hello Sunny Talwar,

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

Thanks,

Burak

sunny_talwar · ‎2018-04-03

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

vishsaggi · ‎2018-04-03

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 · ‎2018-04-03

May be Try

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

burak_ersoy · ‎2018-04-03

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 · ‎2018-04-03

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 · ‎2018-04-03

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 · ‎2018-04-03

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