topic Re: How to convert multiple fields into one single field in App Development

How to convert multiple fields into one single field

thanhng34 — Sat, 16 Nov 2024 18:40:06 GMT

Hello community,

I am currently working on my company's client repository.
There are three fields that determine whether a person is a client of an institution belonging to my company:

- PersonIsClientAGRI: if the person is a client of AGRI -> "1", otherwise "0".
- PersonIsClientCCPMA: if the person is a client of CCPMA -> "1", otherwise "0".
- PersonIsClientCPCEA: if the person is a client of CPCEA -> "1", otherwise "0".

There are many people who are clients of 2 or even 3 institutions.

My need: I would like to "group" the three fields mentioned above into a single field called "Institution", to use it later as a dimension.
For example, for a person who is a client of two institutions "AGRI" and "CCPMA", I will have two lines with the same information relating to this person: a line with "Institution" = "AGRI" and a line with "Institution" = "CCPMA",

instead of a single line with "PersonIsClientAGRI" = "1" and "PersonIsClientCCPMA" = "1" as now.

Before :

ID	personFirstName	personLastName	personDateofBirth	personSex	personIsClientAGRI	personIsClienCCPMA	personIsClientCPCEA
001	Laura	Dubois	15/05/1984	Female	1	1	0

After :

ID	personFirstName	personLastName	personDateofBirth	personSex	Institution
001	Laura	Dubois	15/05/1984	Female	AGRI
001	Laura	Dubois	15/05/1984	Female	CCPMA

Thanks in advance for your help.

Re: How to convert multiple fields into one single field

raji6763 — Mon, 11 May 2020 14:39:19 GMT

Hi thanhng,

Here is the solution for your query.

Steps i followed to achieve the above scenario:

- I have used crosstable function that transform the table and that will convert 3 fields into one field

(institution)

- Load 0 and 1 values as a Status

- Noconcatenate will not concatenate two tables

- I Loaded all fields into Temp table using resident(Status=1).Then drop the initial table and status field.

here is the script

Regards,

raji

Re: How to convert multiple fields into one single field

thanhng34 — Tue, 12 May 2020 06:17:50 GMT

Hi @raji6763,

Thank you so much for your solution, it works great ! Thanks for sharing the script which did facilitate my understanding of how the crosstable does work.

Best regards,

Re: How to convert multiple fields into one single field

raji6763 — Tue, 12 May 2020 06:42:12 GMT

Thank you😊

Re: How to convert multiple fields into one single field

msshawn3019 — Thu, 24 Mar 2022 01:06:37 GMT

Hopefully this finds raji6763 after 2 years... Can you please display the code above crosstable? I'm not sure what you did with the Status field "Load 0 and 1 values as a Status"