pivot the data desired formate

Question

Hi team,

I have data in the below format.

dap_idsap_codecounter_namedistrictcounter_typebrand_nametotal_potential_tytwv_tytrv_tywv_tyrt_ty3725177060595APANG NYANING ENTERPRISEKURUNG KUMEYSUB-DEALERDALMIA280028001603725177060595APANG NYANING ENTERPRISEKURUNG KUMEYSUB-DEALEROTHER28002800603725177060595APANG NYANING ENTERPRISEKURUNG KUMEYSUB-DEALERAMBUJA28002800303725177060595APANG NYANING ENTERPRISEKURUNG KUMEYSUB-DEALERTOPCEM2800280030

Output:

dap_idDLR CODEsap_codecounter_namedistrictcounter_typebrand_nametotal_potential_tyRTWSDALMIA_WSAMBUJA_WSTOPCEM_WSOTH_WSDALMIA_RTAMBUJA_RTTOPCEM_RTOTH_RT37251 77060595APANG NYANING ENTERPRISEKURUNG KUMEYSUB-DEALERDALMIA28028000000160303060

Note: wv_ty = WS and rt_ty = RT

Request you to help the same.

Best Regards,

Kaustubh

CoG · Answer

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!

Sample Workflow.yxmd

MeganBowers · Answer

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.

pivot data.yxmd