How to convert multiple fields into one single field

[thanhng34]

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.

[raji6763 · Accepted solution]

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

[thanhng34]

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,

[raji6763]

Thank you😊

[msshawn3019]

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"