Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Cascader
Creator
Creator

How to convert part of columns from a table to rows?

Hi 

I'm trying to convert columns issue1,issue2,issue3 to one column called issue.

Here is example data what I have:

 
Serial Date issue1 issue2 issue3 desc isCompleted
1 2023-Jan abc xyz   item 1
2 2023-Feb a b c Main Item 0

 

The result I need:

Serial Date issue desc isCompleted
1111 2023-Jan abc item 1
1111 2023-Jan xyz item 1
2222 2023-Feb a Main Item 0
2222 2023-Feb b Main Item 0
2222 2023-Feb c Main Item 0

 

Labels (2)
1 Solution

Accepted Solutions
QFabian
MVP
MVP

Hi @Cascader , please check this example.

It uses CROSSTABLE for transpose the columns to rows.

 

Input:
Load * INLINE [
Serial, Date, issue1, issue2, issue3, desc, isCompleted
1, 2023-Jan, abc, xyz,, item, 1
2, 2023-Feb, a, b, c, Main Item, 0
];

Output:
crosstable(Issue, Desc, 3)
Load
Serial,
Date,
isCompleted,
issue1,
issue2,
issue3
Resident Input;
drop table Input;


noconcatenate
Final:
Load
Serial,
Date,
isCompleted,
Issue,
Desc
Resident Output
Where
not isnull(Desc) and Desc <> ''; // or len(Desc)>0
drop table Output;

Noconcatenate

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

 

CROSSTABLE

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

 

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.

View solution in original post

5 Replies
Diego_780
Creator
Creator

QFabian
MVP
MVP

Hi @Cascader , please check this example.

It uses CROSSTABLE for transpose the columns to rows.

 

Input:
Load * INLINE [
Serial, Date, issue1, issue2, issue3, desc, isCompleted
1, 2023-Jan, abc, xyz,, item, 1
2, 2023-Feb, a, b, c, Main Item, 0
];

Output:
crosstable(Issue, Desc, 3)
Load
Serial,
Date,
isCompleted,
issue1,
issue2,
issue3
Resident Input;
drop table Input;


noconcatenate
Final:
Load
Serial,
Date,
isCompleted,
Issue,
Desc
Resident Output
Where
not isnull(Desc) and Desc <> ''; // or len(Desc)>0
drop table Output;

Noconcatenate

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

 

CROSSTABLE

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptP...

 

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.
QFabian
MVP
MVP

@Diego_780 exactly!

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.
Cascader
Creator
Creator
Author

Thanks QFabian

QFabian
MVP
MVP

good job @Cascader 

Greetings!! Fabián Quezada (QFabian)
did it work for you? give like and mark the solution as accepted.