Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
samantha92
Contributor III
Contributor III

AND clause based on same column

Hiya,

I am trying to create a new cloumn based on field values from another and I am having some issues.

I have a handful of clients who have a service, these clients can have more than one service allocated to them. I need to create an IF and AND statement based on a client ID.

I have a bunch of services here:

And this is the same person with two different services, so I want to group her by her person ID and if she has 'home care' and 'hoem care medication' then she should be in the category ' home care' (which is the new column I want to create).

So essentially I need to create this new column based on the person ID.

I hope I have explained this well enough?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

May be something like this

If(

SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE|') > 0 and

SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE MEDICATION|') > 0, 'Home Care')

View solution in original post

6 Replies
sunny_talwar

Is this something you want to do in the script or front end of the application?

samantha92
Contributor III
Contributor III
Author

Doing it in the script would be more efficient but I'm thinking that front end might give me my results I am after?

I do something similar when working out the total costs for a client:

sum(TOTAL <PERSON_ID> FINANCIAL_WKLY_COST)

So I assumed it would be something similar to this but adding an if statement and removing the sum?

Please correct me if I am wrong!

sunny_talwar

May be something like this

If(

SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE|') > 0 and

SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE MEDICATION|') > 0, 'Home Care')

sunny_talwar

Here PERSON_ID will be your dimension

samantha92
Contributor III
Contributor III
Author

Thanks so much!

Can you tell me how this works please?

sunny_talwar

We are checking if HOME CARE and HOME CARE MEDICATION are one of the values in the concatenated list of services.... if they are... then the value is Home Care