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

Need to create a dataset based on another dataset

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

Preparation

Accepted answers

Joe_Mako

How about the attached workflow?

Add Field

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".

Add Field.yxmd

All comments

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

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

How about the attached workflow?

Add Field

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".

Add Field.yxmd

Hi Joe_Mako ,

Yes that's the perfect solution.

Thank you.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235