Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
thanhng34
Partner - Contributor III
Partner - Contributor III

How to convert multiple fields into one single field

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 :

IDpersonFirstNamepersonLastNamepersonDateofBirthpersonSexpersonIsClientAGRIpersonIsClienCCPMApersonIsClientCPCEA
001LauraDubois15/05/1984Female110

 

After :

IDpersonFirstNamepersonLastNamepersonDateofBirthpersonSexInstitution
001LauraDubois15/05/1984FemaleAGRI
001LauraDubois15/05/1984FemaleCCPMA

 

Thanks in advance for your help.

Labels (2)
1 Solution

Accepted Solutions
raji6763
Creator II
Creator II

 

Hi thanhng,

Here is the solution for your query. solution.png

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

3 filed into 1 transformation.png

Regards,

raji

View solution in original post

4 Replies
raji6763
Creator II
Creator II

 

Hi thanhng,

Here is the solution for your query. solution.png

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

3 filed into 1 transformation.png

Regards,

raji

thanhng34
Partner - Contributor III
Partner - Contributor III
Author

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
Creator II
Creator II

Thank you😊

msshawn3019
Contributor II
Contributor II

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"