topic Re: Filtering a column based on other columns through Expression in QlikView

Filtering a column based on other columns through Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hello Qlik Community,

I have a question about an expression I am trying to figure out to try and accomplish the following:

I have the data set below. My goal is to provide an expression so that if 'Date' and/or 'Code' differs for any one PatientID, then ONLY display those 'PatientIDs'.

PatientID	Date	Code
5708	7/1/2016	1
5708	8/4/2016	1
1285	7/2/2016	2
1285	7/2/2016	3
2120	7/3/2016	3
8462	7/4/2016	4
8462	8/1/2016	8
3002	7/5/2016	5
1098	7/6/2016	6

So with the data set above:

1. PatientID# 5708 would be displayed since there are two different 'Date' records for the same PatientID.

2. PatientID# 1285 would be displayed since there are two different "Code' records for the same PatientID.

3. PatientID# 8462 WOULD NOT be displayed since even though it has 2 records, neither 'Date' nor 'Code' is repeated for that record.

As for the rest of the records, they should ideally not even show up on my straight table chart since there is nothing wrong with them (including 8642 since nothing is wrong with it).

Overall, if 'Date' and/or 'Code' differs for one PatientID, then I would like that PatientID to show up on my straight table chart. If 'Date' and 'Code' do not differ, then I do not want that record to appear on my chart.

I know this can be accomplished and have made some strides towards accomplishing this, using this expression: =if(aggr(NODISTINCT count(Date),PatientID,Code)>1,1,0). But this, however, still shows me the some records like 1098 in the chart above which is just a single record without anything wrong with it and the 8642 example where there is nothing wrong with the record according my condition, but it still shows up in my table.

Thanks in advance! This would be great help for me!

Best Regards,

Tej

Re: Filtering a column based on other columns through Expression

antoniotiman — Thu, 04 Aug 2016 16:32:03 GMT

Hi Tej,

Try Calculated Dimension

=Aggr(DISTINCT If((MinString(Date) = MaxString(Date) and Count(Date) > 1) or (MinString(Code) = MaxString(Code) and Count(Code) > 1),PatientID),PatientID)

check "Suppres when value is Null"

This works within your Data sample, I'm not sure within Your Data

Regards,

Antonio

Re: Filtering a column based on other columns through Expression

sunny_talwar — Thu, 04 Aug 2016 16:34:27 GMT

May be create a straight table like this:

Dimensions

PatientID

Date

Code

Expression:

=If(Count(TOTAL <Date> Date) > 1 or Count(TOTAL <Code> Code) > 1, 1)

Expression can be hidden from the presentation tab

Re: Filtering a column based on other columns through Expression

Thu, 04 Aug 2016 19:08:12 GMT

Hey Antonio,

Thanks for yur reply. I tried applying the calculated dimension who gave me to the exact data source that I provided in the example above, but I am not getting the results that you received when I entered it. I made sure I put the formula in the calculated dimension area and clicked the suppress when value is null option and nothing would show up on the table. Is it maybe because of what I am using for my dimensions?

Please let me know, if you can, why I am not receiving the same reults as you did in your reponse.

Thank you,

Tej

Re: Filtering a column based on other columns through Expression

antoniotiman — Thu, 04 Aug 2016 20:02:44 GMT

See attachment

Re: Filtering a column based on other columns through Expression

Thu, 04 Aug 2016 20:27:16 GMT

Antonio,

Unfortunately, I am using a personal edition of Qlikview until next week so I am unable to open files that are not created by me.

Sorry for the inconvenience. if it is not too much to ask, could you walk me through the steps you did to get the table in the way you did? that woul dbe very helpful to me.

Thank you,

Tej

Re: Filtering a column based on other columns through Expression

Thu, 04 Aug 2016 20:29:16 GMT

Hey Sunny,

I will try it out that way and get back to you.

Thanks,

Tej

Re: Filtering a column based on other columns through Expression

Thu, 04 Aug 2016 20:34:07 GMT

Hey Sunny,

I was able to get my table to look like the yours in the example. Once question I have though is why the patient ID 2120 shows up in the data set when that Patient ID does not have any variation in 'Date' and/or 'Code' as mentioned above?

Please let me know at your earliest convenience.

Thank you,

Tej

Re: Filtering a column based on other columns through Expression

sunny_talwar — Thu, 04 Aug 2016 21:09:22 GMT

Apologize, I mis-interpreted the requirement. Try this:

=If((Count(DISTINCT TOTAL <PatientID> Date) = 1 or Count(DISTINCT TOTAL <PatientID> Code) = 1) and Count(TOTAL <PatientID> PatientID) = 2, 1)

Re: Filtering a column based on other columns through Expression

antoniotiman — Thu, 04 Aug 2016 21:27:16 GMT

Dimensions :

1. Calculated Dimension (as Patient ID)

2. Date

3. Code

Expression : Date

Re: Filtering a column based on other columns through Expression

Mon, 08 Aug 2016 13:55:30 GMT

Thank you both for your help! I was able to get it to work on my end!