How to do Unpivot in Gainsight

Forum|Forum|2 years ago
February 2, 2024
7 replies
110 views

Hi all,

I just want to understand if there a way to unpivot table (data) in Gainsight?

For example, if you look at this image; ID1 has values for ABC and XYZ and ID2 has only for DTE. Left hand side the data we have and on the right hand side is what we need to have

Thanks,

Best answer by romihache

@jigar.vyas
I’m not sure if you can do anything else besides a multiple Union...

Dataset 1, Transform. Select the name and first column. Rename it from ABC to version
Do a case evaluation, string output, call it Unpivot . If field ABC is not null, ABC, else null

Dataset 2 transform, repeat: select the name and second column. Rename it from XYZ to version
Add a a case evaluation, string output, call it Unpivot . If field XYZ is not null, YXZ, else null

Do this until all your columns are “unpivoted”. Union dataset 1 with 2, then that with 3. Having all your datasets with the same column names makes this part easier.
Do a last transform to filter version != null

spencer_engel

Expert ⭐️

I believe you’re asking a similar question as this? Maybe it will help

J

jigar.vyasAuthor

Contributor ⭐️⭐️

@spencer_engel In the above example, all columns are combined in one, but that is not what I am trying to achieve. Basically, I am looking for unpivoting some columns.

romihacheAnswer

VIP ⭐️⭐️⭐️⭐️⭐️

@jigar.vyas
I’m not sure if you can do anything else besides a multiple Union...

Dataset 1, Transform. Select the name and first column. Rename it from ABC to version
Do a case evaluation, string output, call it Unpivot . If field ABC is not null, ABC, else null

Dataset 2 transform, repeat: select the name and second column. Rename it from XYZ to version
Add a a case evaluation, string output, call it Unpivot . If field XYZ is not null, YXZ, else null

Do this until all your columns are “unpivoted”. Union dataset 1 with 2, then that with 3. Having all your datasets with the same column names makes this part easier.
Do a last transform to filter version != null

Continuous improvement is better than delayed perfection

J

jigar.vyasAuthor

Contributor ⭐️⭐️

@romihache I think you’re right. That’s the only way here though lengthy. Expected straight forward option like we have in excel using power query editor where can just select the columns to be unpivoted and select the option Unpivot Columns and everything is done in a few seconds. I wish Gainsight had it but they can take it a feature request.