Seperating and columnizing

Study · ‎2022-10-28

Good day to everyone,

I have a table column that looks like this:

Research - 4; Teaching - 3; Design - 4; Writing - 4; Strategic Planning and Execution - 3;

Research - 4; Teaching - 4; Design - 4; Writing - 4; Strategic planning and execution - 4;

Research - 4; Design Knowledge - 4; Capable of understanding Building Codes - 3; Consulting with Engineers - 4;

Research - 4; Design Knowledge - 4; Understanding Building Codes - 3; Understanding Structural Engineer Data - 4;

That I would like to organize like this

Research	Teaching	Design	Writing	Strategic Planning and Execution	Design Knowledge	Understanding Building Codes	Consulting with Engineers	Understanding Structural Engineer Data
4	3	4	4	3
4	3	4	4	3
4	4	4	4	4
4					4	3	4
4					4	3		4

Is there a way to accomplish this in the backend of QlikSense?

MarcoWedel · ‎2022-10-28

maybe like this?

table1:
LOAD ID,
     Trim(SubField(CompScore,'-',1)) as Competency,
     Trim(SubField(CompScore,'-',2)) as Score
Where Len(Trim(CompScore));
LOAD RecNo() as ID,
     SubField(@1,';') as CompScore
Inline [
"Research - 4; Teaching - 3; Design - 4; Writing - 4; Strategic Planning and Execution - 3;"
"Research - 4; Teaching - 3; Design - 4; Writing - 4; Strategic Planning and Execution - 3;"
"Research - 4; Teaching - 4; Design - 4; Writing - 4; Strategic planning and execution - 4;"
"Research - 4; Design Knowledge - 4; Capable of understanding Building Codes - 3; Consulting with Engineers - 4;"
"Research - 4; Design Knowledge - 4; Understanding Building Codes - 3; Understanding Structural Engineer Data - 4;"
](no labels);

Study · ‎2022-11-01

Thank you very much for this. Is there an easy way to perform a pivot in the back end?

MarcoWedel · ‎2022-11-02

Yes, to create all those competencies as individual fields, you just have to add the generic prefix to this load:

table1:
Generic
LOAD ID,
     Trim(SubField(CompScore,'-',1)) as Competency,
     Trim(SubField(CompScore,'-',2)) as Score
Where Len(Trim(CompScore));
LOAD RecNo() as ID,
     SubField(@1,';') as CompScore
Inline [
"Research - 4; Teaching - 3; Design - 4; Writing - 4; Strategic Planning and Execution - 3;"
"Research - 4; Teaching - 3; Design - 4; Writing - 4; Strategic Planning and Execution - 3;"
"Research - 4; Teaching - 4; Design - 4; Writing - 4; Strategic planning and execution - 4;"
"Research - 4; Design Knowledge - 4; Capable of understanding Building Codes - 3; Consulting with Engineers - 4;"
"Research - 4; Design Knowledge - 4; Understanding Building Codes - 3; Understanding Structural Engineer Data - 4;"
](no labels);

Data Load Editor