hi all, i have a data set looks like below, this is only 4 month period so in the end there will be 24 month data. StatusCountEnd Date (Updated)Pending Action201_Apr_2020Pending Action7101_Apr_2021Pending Action108_Apr_2020Pending Action6808_Apr_2021Pending Action115_Apr_2020Pending Action5415_Apr_2021Pending Action8822_Apr_2021Pending Action6229_Apr_2021Pending Receipt101_Apr_2020Pending Receipt5101_Apr_2021Pending Receipt5208_Apr_2021Pending Receipt5615_Apr_2021Pending Receipt6422_Apr_2021Pending Receipt5129_Apr_2021Rejected301_Apr_2020Rejected2601_Apr_2021Rejected3308_Apr_2021Rejected3115_Apr_2021Rejected222_Apr_2020Rejected4222_Apr_2021Rejected229_Apr_2020Rejected2929_Apr_2021Pending Action1904_Feb_2020Pending Action7504_Feb_2021Pending Action1118_Feb_2020Pending Action5018_Feb_2021Pending Action1125_Feb_2020Pending Action5725_Feb_2021Pending Receipt1404_Feb_2020Pending Receipt8704_Feb_2021Pending Receipt1118_Feb_2020Pending Receipt10318_Feb_2021Pending Receipt1125_Feb_2020Pending Receipt6325_Feb_2021Rejected704_Feb_2020Rejected3404_Feb_2021Rejected918_Feb_2020Rejected4218_Feb_2021Rejected625_Feb_2020Rejected1625_Feb_2021 so after using cross tab tool, is it possible to have something looks like below? thanks so much guys Status04_Feb_202004_Feb_202118_Feb_202018_Feb_202125_Feb_202025_Feb_202101_Apr_202001_Apr_202108_Apr_202008_Apr_202115_Apr_202015_Apr_202122_Apr_202022_Apr_202129_Apr_202029_Apr_2021Pending Action197511501157271168154 88 62Pending Receipt1487111031163151 52 56 64 51Rejected734942616326 33 31242229

Cross tab for different date month year into a specific format

hi all,

i have a data set looks like below, this is only 4 month period so in the end there will be 24 month data.

Status	Count	End Date (Updated)
Pending Action	2	01_Apr_2020
Pending Action	71	01_Apr_2021
Pending Action	1	08_Apr_2020
Pending Action	68	08_Apr_2021
Pending Action	1	15_Apr_2020
Pending Action	54	15_Apr_2021
Pending Action	88	22_Apr_2021
Pending Action	62	29_Apr_2021
Pending Receipt	1	01_Apr_2020
Pending Receipt	51	01_Apr_2021
Pending Receipt	52	08_Apr_2021
Pending Receipt	56	15_Apr_2021
Pending Receipt	64	22_Apr_2021
Pending Receipt	51	29_Apr_2021
Rejected	3	01_Apr_2020
Rejected	26	01_Apr_2021
Rejected	33	08_Apr_2021
Rejected	31	15_Apr_2021
Rejected	2	22_Apr_2020
Rejected	42	22_Apr_2021
Rejected	2	29_Apr_2020
Rejected	29	29_Apr_2021
Pending Action	19	04_Feb_2020
Pending Action	75	04_Feb_2021
Pending Action	11	18_Feb_2020
Pending Action	50	18_Feb_2021
Pending Action	11	25_Feb_2020
Pending Action	57	25_Feb_2021
Pending Receipt	14	04_Feb_2020
Pending Receipt	87	04_Feb_2021
Pending Receipt	11	18_Feb_2020
Pending Receipt	103	18_Feb_2021
Pending Receipt	11	25_Feb_2020
Pending Receipt	63	25_Feb_2021
Rejected	7	04_Feb_2020
Rejected	34	04_Feb_2021
Rejected	9	18_Feb_2020
Rejected	42	18_Feb_2021
Rejected	6	25_Feb_2020
Rejected	16	25_Feb_2021

so after using cross tab tool, is it possible to have something looks like below? thanks so much guys

Status	04_Feb_2020	04_Feb_2021	18_Feb_2020	18_Feb_2021	25_Feb_2020	25_Feb_2021	01_Apr_2020	01_Apr_2021	08_Apr_2020	08_Apr_2021	15_Apr_2020	15_Apr_2021	22_Apr_2020	22_Apr_2021	29_Apr_2020	29_Apr_2021
Pending Action	19	75	11	50	11	57	2	71	1	68	1	54		88		62
Pending Receipt	14	87	11	103	11	63	1	51		52		56		64		51
Rejected	7	34	9	42	6	16	3	26		33		31	2	42	2	29

Transformation

Preparation

Accepted answers

Qiu

@Haokun
Inspired by @AkimasaKajitani

Crosstab_AK_2.yxmd

All comments

AkimasaKajitani

Hi @Haokun ,

You can use CrossTab Tool to achieve what you want.

Crosstab_AK.yxmd

ArtApa

Hi @Haokun - The solution posted by Akimasa will sort columns in alphabetical order. Let us know, if you want to sort them differently, e.g. chronological order.

AkimasaKajitani

Hi @Haokun @ArtApa

I made a new workflow that sorted chronologically.

I change the type of date and pivot and then rewrite the field names by dynamic rename tool.

Crosstab_AK_2.yxmd

Haokun

@AkimasaKajitani @ArtApa

that is not I am looking for...i don't want to have chronological order, i want to follow the output structure, so that it is able to compare year 2020 and year 2021 for different month. So the date and month will be in chronological order, not the year.

Qiu

@Haokun
Inspired by @AkimasaKajitani

Crosstab_AK_2.yxmd

Haokun

@Qiu it works man....thanks again for your help!

Qiu

@Haokun

Glad to help and thank you for the accept mark.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703