new row for every column having a value

Question

Hi All, I am trying to perform transpose where i want a row for each record id where there is a value populated in any on of the columns against this records.

Sample Data:

record idrecord typerecord categorycolumn 1column 2column 3column 4R1RTARC1C1C2  R2RTARC1 C2C3C4R3RTBRC5  C3

Required Output:

record idrecord typerecord categorycolumn 1R1RTARC1C1R1RTARC1C2R2RTARC1C2R2RTARC1C3R2RTARC1C4R3RTBRC5C3

apathetichell · Accepted Answer

transpose tool:

record id, record type and record category are key columns.

filter tool:

!isempty([value])

select tool:

rename [name] column 1

atcodedog05 · Accepted Answer

Hi @Swati_Kejriwal

Here is how you can do it.

Workflow:

1. Using transpose converting columns to rows.

2. Using data cleanse tool to clean values column.

3. Filtering of empty values.

4. Using filter tool to keep only required columns.

Hope this helps : )