Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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,
Thank you😊
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"