Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be something like this
If(
SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE|') > 0 and
SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE MEDICATION|') > 0, 'Home Care')
Is this something you want to do in the script or front end of the application?
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!
May be something like this
If(
SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE|') > 0 and
SubStringCount(Concat(DISTINCT '|' & Service & '|'), '|HOME CARE MEDICATION|') > 0, 'Home Care')
Here PERSON_ID will be your dimension
Thanks so much!
Can you tell me how this works please?
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