SOLVED
Transformation Help
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
AG21
5 - Atom
‎03-29-2021
03:46 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi community,
I have data structured as follows:
Customer ID | Attribute | Month 1 | Month 2 | Month 3 |
Customer 1 | Amount | $6 | $5 | $4 |
Customer 1 | Margin | 2% | 3% | 2% |
Customer 1 | Sale Date | Date 1 | Date 2 | Date 3 |
Customer 2 | Amount | $3 | $2 | $1 |
Customer 2 | Margin | 4% | 4% | 5% |
Customer 2 | Sale Date | Date 4 | Date 5 | Date 6 |
And need the data in the following format for analysis:
Sale Date | Customer | Amount | Margin |
Date 1 | Customer 1 | $6 | 2% |
Date 2 | Customer 1 | $5 | 3% |
Date 3 | Customer 1 | $4 | 2% |
Date 4 | Customer 2 | $3 | 4% |
Date 5 | Customer 2 | $2 | 4% |
Date 6 | Customer 2 | $1 | 5% |
I've tried doing this with the Tile tool and Transpose tool but can't seem to get it configured right. Ideas would be MUCH appreciated. Thank you.
Solved! Go to Solution.
Labels:
- Labels:
- Datasets
- Preparation
- Transformation
3 REPLIES 3
Luke_C
17 - Castor
‎03-29-2021
04:13 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @AG21
Take a look at this solution. I parse out the sales dates, cross tab the amount/margin, and then join them back.
‎03-29-2021
04:41 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Luke_C you are my hero.
Thank you so much.
Luke_C
17 - Castor
‎03-29-2021
04:54 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Happy to help!