Data Prep Transforms

Janet_21505

Is it possible to transform the card numbers below to report across vs down for each account number?  I would like to see one row for the account number 0174 with the next 2 columns reporting each card number.  I have worked with Pivot vs Unpivot, but can't quite get the correct output. 

 

Janet

CPorthouse

Another solution would be to add a row number column to your current table

which would give you something like this:

Use the Transform->Group function

To group by DDA and only select the minimum row number:

Output:

Using the Combine->Join Tables, Put your first sheet on the left and the GroupBy table on the right:

and the "Click to join".

Select type "Lookup" and use the DDA column for your matching key pairs and only select the minimum row number for the output on the right side:

You should get a result similar to this:

Now, add a formula column:

And use this as your formula:

[Row Number]-[Minimum(Row Number)]+1

You can then use Combine->Pivot to pivot on the Formula column and group on the DDA column.  For the aggregate section, select Card # and "First" as the type:

 

This should result in:

 

 

 

CPorthouse

Another solution would be to add a row number column to your current table

which would give you something like this:

Use the Transform->Group function

To group by DDA and only select the minimum row number:

Output:

Using the Combine->Join Tables, Put your first sheet on the left and the GroupBy table on the right:

and the "Click to join".

Select type "Lookup" and use the DDA column for your matching key pairs and only select the minimum row number for the output on the right side:

You should get a result similar to this:

Now, add a formula column:

And use this as your formula:

[Row Number]-[Minimum(Row Number)]+1

You can then use Combine->Pivot to pivot on the Formula column and group on the DDA column.  For the aggregate section, select Card # and "First" as the type:

 

This should result in:

 

 

 

Janet_21505

Believe it or not, 2 months after posting this question, I had to circle back to the answer.  I am again impressed beyond comprehension!  I followed your incredible directions and produced exactly what I needed.  How in the heck do you guys come up with this stuff!!  Data Prep Support ROCKS!!!