Data Prep & Transpose

[Scheruku]

Hello All,

I have a file where each customer has different dates. There are other columns having Aging status with amount. 

We are trying to represent for each customer the date of disconnect status in transpose/crosstab, and the os amount for each disconnect date has to be taken from the aging column. If the number of days are 219, the aging amount for the disconnect date will be from 180-365 days column.

Cust	Port	Date1	Status	DateCutOff	Days	30-60 days	60-90 days	90-120 days	120-180 days	180-365 days	Above 365 days
1148	TG	23-Feb-17	Disc	30-09-2017	219	8689.01	3548.95	4306.75	21395.46	2574.47	0
1148	TG	26-Jul-17	Disc	30-09-2017	66	8689.01	3548.95	4306.75	21395.46	2574.47	0
1148	TG	03-Aug-17	Disc	30-09-2017	58	8689.01	3548.95	4306.75	21395.46	2574.47	0
1148	TG	01-Sep-17	Disc	30-09-2017	29	8689.01	3548.95	4306.75	21395.46	2574.47	0
67600	TG	21-Jun-17	Disc	30-09-2017	101	972.98	1863.95	3241.61	3104.24	1050.87	1.97
67600	TG	08-Jul-17	Disc	30-09-2017	84	972.98	1863.95	3241.61	3104.24	1050.87	1.97
67787	TA	02-Aug-17	Disc	30-09-2017	59	400	890	767	267	456	312

Output

Cust

Disc1

Os/ amount

Disc2

Os/ amount

Disc3

Os/ amount

Disc4

Os/ amount

Disc5

Os/ amount

Disc6

Os/ amount

1148

23-Feb-17

2574.47

26-Jul-17

3548.95

03-Aug-17

8689.01

01-Sep-17

8689.01

67600

21-Jun-17

3241.61

08-Jul-17

1863.95

67787

02-Aug-17

400

Attached the sample data. Please help. 

[JoshKushner · Accepted solution]

First calculate the Os/amount in a separate column for each customer and date. Consolidate all your dates and amounts it's into a single column (values column). Then define the headers for the values column in another column (each Os/amount column will need a distinct header. Try 'Os/amount disc 1' or something). Then cross tab the headers and date and Os/amount grouped by the customer number.

Let me know if that helps!

Sorry there's not example attached. I'm not by a computer

[Scheruku]

Hi @Josh_dup_166

Thank you so much for your reply. I was able to separate the dates for each customer, but the other steps are little hard to follow. If possible, Could you please post an example or workflow. 

Cust	Date_1	Date_2	Date_3	Date_4	Date_5	Date_6	Date_7	Date_8
1148	28-01-2017
1148	04-02-2017	09-06-2017
1148	07-05-2017	08-06-2017	08-07-2017

[Scheruku]

Hi @JoshKushner

Thank you very very very much for you guidance. I read through your logic again and again, and was able to reach to the desired output. Thank you so much for pointing me in the right direction.

Best Regards,

Scheruku