pivot the data desired formate

[Kaustubh17]

Hi team,

I have data in the below format.

dap_id	sap_code	counter_name	district	counter_type	brand_name	total_potential_ty	twv_ty	trv_ty	wv_ty	rt_ty
37251	77060595	APANG NYANING ENTERPRISE	KURUNG KUMEY	SUB-DEALER	DALMIA	280	0	280	0	160
37251	77060595	APANG NYANING ENTERPRISE	KURUNG KUMEY	SUB-DEALER	OTHER	280	0	280	0	60
37251	77060595	APANG NYANING ENTERPRISE	KURUNG KUMEY	SUB-DEALER	AMBUJA	280	0	280	0	30
37251	77060595	APANG NYANING ENTERPRISE	KURUNG KUMEY	SUB-DEALER	TOPCEM	280	0	280	0	30

Output:

dap_id

DLR CODE

sap_code

counter_name

district

counter_type

brand_name

total_potential_ty

RT

WS

DALMIA_WS

AMBUJA_WS

TOPCEM_WS

OTH_WS

DALMIA_RT

AMBUJA_RT

TOPCEM_RT

OTH_RT

37251

77060595

APANG NYANING ENTERPRISE

KURUNG KUMEY

SUB-DEALER

DALMIA

280

280

0

0

0

0

0

160

30

30

60

Note: wv_ty = WS and rt_ty = RT

Request you to help the same. 

Best Regards,

Kaustubh

[MeganBowers]

Hi @Kaustubh17,

You can use cross-tab tools and dynamic renames to get your data into the structure above. If you want the brand name, RT, and WS as columns as well in the output, you would have to join back in the original dataset.

 

[CoG]

When trying to Cross Tab multiple fields at once, it is generally more efficient to Transpose the data first so that you can avoid redundant tools and unnecessary joins:

Happy Solving!