topic Re: Set Analysis to filter out 3 digit zipcodes in QlikView

Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 13:51:12 GMT

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

Re: Set Analysis to filter out 3 digit zipcodes

sunny_talwar — Tue, 03 Apr 2018 13:55:53 GMT

See if this works

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

Re: Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 14:04:14 GMT

Hello Sunny Talwar,

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

Thanks,

Burak

Re: Set Analysis to filter out 3 digit zipcodes

sunny_talwar — Tue, 03 Apr 2018 14:06:45 GMT

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

Re: Set Analysis to filter out 3 digit zipcodes

vishsaggi — Tue, 03 Apr 2018 14:27:56 GMT

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)

Re: Set Analysis to filter out 3 digit zipcodes

sasiparupudi1 — Tue, 03 Apr 2018 14:38:53 GMT

May be Try

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

Re: Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 15:01:39 GMT

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.

Re: Set Analysis to filter out 3 digit zipcodes

sunny_talwar — Tue, 03 Apr 2018 15:13:47 GMT

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

Re: Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 15:13:57 GMT

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

Re: Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 15:22:43 GMT

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

Re: Set Analysis to filter out 3 digit zipcodes

Digvijay_Singh — Tue, 03 Apr 2018 15:29:25 GMT

I checked this expression in Feb 2018 with a small sample, it works for me. See attached.

Re: Set Analysis to filter out 3 digit zipcodes

sunny_talwar — Tue, 03 Apr 2018 15:29:48 GMT

I am using Feb 2018 Patch 1 here

and my mock up isn't showing any problems here

Re: Set Analysis to filter out 3 digit zipcodes

marcus_sommer — Tue, 03 Apr 2018 15:34:38 GMT

If there are not too many different lengths of your zip-code you could try to reverse your approach with something like:

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

which would only count zip-codes with 4 or 5 chars.

Edit: If I look on the examples from Sunny and Digvijay I wonder if your 3 digit zip-code really contained 3 chars. Therefore I would be useful to check the length of them and if they are different like expected to investigate the real content of these fieldvalues.

- Marcus

Re: Set Analysis to filter out 3 digit zipcodes

burak_ersoy — Tue, 03 Apr 2018 15:47:47 GMT

Sunny Talwar,

Thank you for your response. I imported the example app in my environment and it looks like all the expressions are working just fine. I am not sure why the script is not working in my app; however, I am not able to share the app in here unfortunately. I will take the suggestions from everyone and try to solve this by myself. Thank you for sharing the example app again.

All,

Thank you for all your responses and recommendations.

Re: Set Analysis to filter out 3 digit zipcodes

MarcoWedel — Tue, 03 Apr 2018 20:10:33 GMT

other possible set expressions could be:

{$<PAT_ZIP={"(not like ???)"}>}

{$<PAT_ZIP={"=not PAT_ZIP like '???'"}>}

Re: Set Analysis to filter out 3 digit zipcodes

wdchristensen — Tue, 03 Apr 2018 21:48:47 GMT

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!