Need to create a dataset based on another dataset

[HA]

Hi all,

I have a dataset

Security	Field	Value
CT2 Govt	PX_MID	99.871094
CT2 Govt	PX_LAST	99.875
CT2 Govt	PX_BID	99.867188
CT2 Govt	CHG_CLS_BID	0.039063
CT2 Govt	PX_EVAL	99.867188
CT2 Govt	CHG_PCT_1D	0.043
CT2 Govt	YLD_YTM_MID	0.817
CT2 Govt	YLD_CNV_MID	0.817
CT2 Govt	PRIOR_CLOSE_MID_YLD	0.836843
CT2 Govt	ID_BB_UNIQUE	GV912828T42
CT3 Govt	PX_MID	100.066406
CT3 Govt	PX_LAST	100.070313
CT3 Govt	PX_BID	100.0625
CT3 Govt	CHG_CLS_BID	0.078125
CT3 Govt	PX_EVAL	100.0625
CT3 Govt	CHG_PCT_1D	0.082
CT3 Govt	YLD_YTM_MID	0.977
CT3 Govt	YLD_CNV_MID	0.977
CT3 Govt	PRIOR_CLOSE_MID_YLD	1.003978
CT3 Govt	ID_BB_UNIQUE	GV912828T59

and I need to create the following dataset

Security	Field	Value	BB Unique ID
CT2 Govt	PX_MID	99.871094	GV912828T42
CT2 Govt	PX_LAST	99.875	GV912828T42
CT2 Govt	PX_BID	99.867188	GV912828T42
CT2 Govt	CHG_CLS_BID	0.039063	GV912828T42
CT2 Govt	PX_EVAL	99.867188	GV912828T42
CT2 Govt	CHG_PCT_1D	0.043	GV912828T42
CT2 Govt	YLD_YTM_MID	0.817	GV912828T42
CT2 Govt	YLD_CNV_MID	0.817	GV912828T42
CT2 Govt	PRIOR_CLOSE_MID_YLD	0.836843	GV912828T42
CT2 Govt	ID_BB_UNIQUE	GV912828T42	GV912828T42
CT3 Govt	PX_MID	100.066406	GV912828T59
CT3 Govt	PX_LAST	100.070313	GV912828T59
CT3 Govt	PX_BID	100.0625	GV912828T59
CT3 Govt	CHG_CLS_BID	0.078125	GV912828T59
CT3 Govt	PX_EVAL	100.0625	GV912828T59
CT3 Govt	CHG_PCT_1D	0.082	GV912828T59
CT3 Govt	YLD_YTM_MID	0.977	GV912828T59
CT3 Govt	YLD_CNV_MID	0.977	GV912828T59
CT3 Govt	PRIOR_CLOSE_MID_YLD	1.003978	GV912828T59
CT3 Govt	ID_BB_UNIQUE	GV912828T59	GV912828T59

repeating ID column for matching securities

[roadhouse]

Try creating the new column with the Formula Tool.  For the logic of the new column you'll want to put:

If Security = X then Y

else if Security = A then B

[HA]

Hi,

I cannot use that meethod because for three reassons

1) there are  over 100 securities

2) Securities change all teh time

3) Unique IDs per sequirity change over time

The solution should be dynamic

[Joe_Mako · Accepted solution]

How about the attached workflow?

It uses Filter to only keep records where [Field]="ID_BB_UNIQUE", and then joins that back into the data on Security, renaming the Value field to "BB Unique ID".

[HA]

Hi Joe_Mako ,

Yes that's the perfect solution.

Thank you.