Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
CROSSTABLE
I think that Crosstable is what you want for this situation:
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
CROSSTABLE
@DiegoF exactly!
Thanks QFabian
good job @Cascader