Qlik Community

Qlik Sense App Development

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: How to convert multiple fields into one single field

 

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

3 Replies
Highlighted
Creator
Creator

Re: How to convert multiple fields into one single field

 

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

Highlighted
Partner
Partner

Re: How to convert multiple fields into one single field

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,

 

Highlighted
Creator
Creator

Re: How to convert multiple fields into one single field

Thank you😊