single input file; 2 dates within; basic "day1" vs "day2" comparison

[CarterJ]

What is the best way to transform a single input file into a basic 'day1' vs 'day2' comparison when both dates are included in the same file and a master list must be created first because there might not be data for every primary key and every date?

If it were two input files each with a different date it would be no problem to join them (and also see which records didn't join (L & R) but in this case the files are already combined, and the data stacked.

I could first break the input file into 2 files based on date and then join them but it seems like there's a better way.

In excel I'd create a master list (remove duplicates on Abv, Key, Name and move that master list to another sheet and then concatenate Abv+Date to create a primary key for vlookup on the data I wanted. The master list could change as the dates roll.. ex: an Abv may drop on a day or a new Abv might appear on a given day. There will always be only 2 dates however.

Input File:

"Abv" and "Date" would be the primary key

Abv	Key	Name	Date	Value	Units	Amount
JZI	6005	Utah Fund - I Class	03/18/2024	12.31	5,407,684.05	66,551,939.90
JZI	6005	Utah Fund - I Class	03/19/2024	12.37	5,401,677.66	66,845,412.40
ZJZ	6005	Utah Fund - Z Class	03/18/2024	12.36	5,236,623.56	64,711,392.21
ZJZ	6005	Utah Fund - Z Class	03/19/2024	12.43	5,231,798.42	65,010,551.20
JZF	6005	Utah Fund	03/18/2024	12.28	16,515,581.20	202,799,740.98
JZF	6005	Utah Fund	03/19/2024	12.35	16,444,139.56	203,036,966.15
AHI	5008	Dirt Fund - I Class	03/18/2024	58.63	206,450,662.12	12,103,813,077.96
AHI	5008	Dirt Fund - I Class	03/19/2024	59.03	206,332,662.80	12,180,322,717.17
ZAH	5008	Dirt Fund - Z Class	03/18/2024	59.38	40,081,796.83	2,380,094,977.51
ZAH	5008	Dirt Fund - Z Class	03/19/2024	59.79	40,091,481.70	2,397,131,209.54
AHF	5008	Dirt Fund	03/18/2024	58.28	176,541,801.02	10,288,278,037.41
AHF	5008	Dirt Fund	03/19/2024	58.68	176,413,399.03	10,351,664,705.04
AEI	5009	Time Warp Fund - I Class	03/18/2024	39.30	39,677,939.07	1,559,275,475.01
AEI	5009	Time Warp Fund - I Class	03/19/2024	39.58	39,682,129.54	1,570,768,774.03
AEF	5009	Time Warp Fund	03/18/2024	39.33	26,503,981.79	1,042,374,520.03
AEF	5009	Time Warp Fund	03/19/2024	39.61	26,499,084.16	1,049,747,788.21
AII	5010	Long Dollar Fund - I Class	03/18/2024	7.89	320,885,292.90	2,531,839,123.64
AII	5010	Long Dollar Fund - I Class	03/19/2024	7.91	320,891,047.69	2,537,179,865.84
AIR	5010	Long Dollar Fund - R Class	03/18/2024	7.89	130,467.59	1,029,542.04
AIR	5010	Long Dollar Fund - R Class	03/19/2024	7.91	130,546.68	1,032,320.68
ZAI	5010	Long Dollar Fund - Z Class	03/18/2024	7.89	1,693,005,267.00	13,358,863,614.18
ZAI	5010	Long Dollar Fund - Z Class	03/19/2024	7.91	1,692,908,010.65	13,386,034,194.63
AIZ	5010	Long Dollar Fund - Advisor Class	03/18/2024	7.88	1,001,970.37	7,891,120.73
AIZ	5010	Long Dollar Fund - Advisor Class	03/19/2024	7.89	1,001,228.23	7,901,764.42
AIF	5010	Long Dollar Fund	03/18/2024	7.89	92,561,062.70	730,695,905.37
AIF	5010	Long Dollar Fund	03/19/2024	7.91	92,502,172.96	731,758,450.43
QSI	5011	Sinking Fund - I Class	03/18/2024	96.46	150,300,887.34	14,498,636,621.85
QSI	5011	Sinking Fund - I Class	03/19/2024	97.08	150,242,858.51	14,585,146,406.58
QSR	5011	Sinking Fund - R Class	03/18/2024	87.66	8,013,070.77	702,400,225.51

Output Table (may look different using Alteryx, but the concept is that the D1 and D2 data is on the same row for each record in the master list), last record shows 100% change where Day1 has no data:

			03/19/2024	03/19/2024	03/19/2024	03/18/2024	03/18/2024	03/18/2024	Change	Change	Change
			Day1	Day1	Day1	Day2	Day2	Day2	D1-D2	D1-D2	D1-D2
Abv	Key	Name	Value	Units	Amount	Value	Units	Amount	Value	Units	Amount
JZI	6005	Utah Fund - I Class	12.37	5,401,677.66	66,845,412.40	12.31	5,407,684.05	66,551,939.90	0.06	(6,006.40)	293,472.50
ZJZ	6005	Utah Fund - Z Class	12.43	5,231,798.42	65,010,551.20	12.36	5,236,623.56	64,711,392.21	0.07	(4,825.14)	299,158.99
JZF	6005	Utah Fund	12.35	16,444,139.56	203,036,966.15	12.28	16,515,581.20	202,799,740.98	0.07	(71,441.63)	237,225.17
AHI	5008	Dirt Fund - I Class	59.03	206,332,662.80	12,180,322,717.17	58.63	206,450,662.12	12,103,813,077.96	0.40	(117,999.32)	76,509,639.21
ZAH	5008	Dirt Fund - Z Class	59.79	40,091,481.70	2,397,131,209.54	59.38	40,081,796.83	2,380,094,977.51	0.41	9,684.87	17,036,232.03
AHF	5008	Dirt Fund	58.68	176,413,399.03	10,351,664,705.04	58.28	176,541,801.02	10,288,278,037.41	0.40	(128,401.99)	63,386,667.63
AEI	5009	Time Warp Fund - I Class	39.58	39,682,129.54	1,570,768,774.03	39.30	39,677,939.07	1,559,275,475.01	0.28	4,190.47	11,493,299.02
AEF	5009	Time Warp Fund	39.61	26,499,084.16	1,049,747,788.21	39.33	26,503,981.79	1,042,374,520.03	0.28	(4,897.63)	7,373,268.18
AII	5010	Long Dollar Fund - I Class	7.91	320,891,047.69	2,537,179,865.84	7.89	320,885,292.90	2,531,839,123.64	0.02	5,754.79	5,340,742.20
AIR	5010	Long Dollar Fund - R Class	7.91	130,546.68	1,032,320.68	7.89	130,467.59	1,029,542.04	0.02	79.10	2,778.64
ZAI	5010	Long Dollar Fund - Z Class	7.91	1,692,908,010.65	13,386,034,194.63	7.89	1,693,005,267.00	13,358,863,614.18	0.02	(97,256.35)	27,170,580.45
AIZ	5010	Long Dollar Fund - Advisor Class	7.89	1,001,228.23	7,901,764.42	7.88	1,001,970.37	7,891,120.73	0.01	(742.14)	10,643.69
AIF	5010	Long Dollar Fund	7.91	92,502,172.96	731,758,450.43	7.89	92,561,062.70	730,695,905.37	0.02	(58,889.75)	1,062,545.06
QSI	5011	Sinking Fund - I Class	97.08	150,242,858.51	14,585,146,406.58	96.46	150,300,887.34	14,498,636,621.85	0.62	(58,028.83)	86,509,784.73
QSR	5011	Sinking Fund - R Class	0.00	-	-	87.66	8,013,070.77	702,400,225.51	-87.66	(8,013,070.77)	(702,400,225.51)

[mgaronson · Accepted solution]

This is ugly, however, it does work.You can make the missing date data as zero on your own and calculation will work.

[CarterJ]

Thanks very much. This is a very interesting approach and indeed does work! Seems the key is tagging a row as 1 or 2 representing Day1 or Day2 and from there the cross tab does the 'breaking apart' to be rejoined again. I'll test this some more.