topic Re: AND clause based on same column in QlikView

AND clause based on same column

samantha92 — Tue, 10 Jul 2018 10:24:36 GMT

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

Re: AND clause based on same column

sunny_talwar — Tue, 10 Jul 2018 11:36:32 GMT

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

Re: AND clause based on same column

samantha92 — Tue, 10 Jul 2018 12:50:14 GMT

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!

Re: AND clause based on same column

sunny_talwar — Tue, 10 Jul 2018 12:54:19 GMT

May be something like this

If(

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

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

Re: AND clause based on same column

sunny_talwar — Tue, 10 Jul 2018 12:54:41 GMT

Here PERSON_ID will be your dimension

Re: AND clause based on same column

samantha92 — Tue, 10 Jul 2018 13:20:42 GMT

Thanks so much!

Can you tell me how this works please?

Re: AND clause based on same column

sunny_talwar — Tue, 10 Jul 2018 13:24:45 GMT

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