Synthetic keys problems: composite and original fields

[#1]

Hi,

I'm joining 2 tables with 3 fields in common: course code, level, survey year.  Of course I'm getting a synthetic key for the 3 variables.  I understand that I can concatenate the four fields into one composite field and rename the constituent fields in the second table.

However, I still want to be able to use the original 3 fields from both tables as separate fields in my dashboards.  How can I do this without having synthetic keys.

Any help gratefully received,

Regards,

Racine62

[PrashantSangle]

Hi,

Try this,

You can Rename one of the SURVYEAR from SES or NSS2.

Regards

[#3]

Thanks for such a quick response,

But I still want to have one field with all the SURVYEAR values:

SES2011, SES2012, SES2013 from the SES table & NSS2013 from the NSS table.

[its_anandrjs]

Hi,

Rename that field otherwise make composite key with that fields.

Hope this helps

Thanks & Regards

[its_anandrjs]

Hi,

You can rename on of the field SURVYEAR from any table like from SES or NSS2 like

SURVYEAR  as SURV_YEAR

then synthetic key is not created

Hope this helps

Thanks & Regards

[Gysbert_Wassenaar]

I only see two common fields in the document you posted. What you can do is create a link table.

SES:

LOAD COURSECODE&YEAR&'SES' as %Key, ...other Fields

from SES_source;

NSS2:

LOAD COURSECODE&YEAR&'NSS2' as %Key, ...other Fields

from NSS_source;

LINK:

LOAD distinct COURSECODE&YEAR&'SES' as %Key, COURSECODE, YEAR, 'SES' as Source

resident SES;

concatenate(LINK)

LOAD distinct COURSECODE&YEAR&'NSS2' as %Key, COURSECODE, YEAR, 'NSS2' as Source

resident NSS2;

drop fields COURSECODE, YEAR from SES, NSS2;

You now still have all the coursecode and year values from both tables, but in the table LINK

[its_anandrjs]

Hi,

In Another way you can do like this

Load

fieldname.....

COURSECODE as New_COURSECODE,

SURVYEAR as New_SURVYEAR,

COURSECODE&'|'&SURVYEAR as %LinkKey

from Location;

load

COURSECODE ,

SURVYEAR,

COURSECODE&'|'&SURVYEAR as %LinkKey

from location;

and from this your COURSECODE , SURVYEAR fields is still there and you can use that one also

Hope this helps

Thanks & Regards

[PrashantSangle]

Hi,

I saw you script

In that You can create

In NSS2 table

'NSS' & NSSYEAR AS NSS_SURVYEAR,

In SES table

'SES' & SESYEAR AS SES_SURVYEAR

and Made changes in

SURVEYYEAR table as

if(Left(SURVYEAR,3)='NSS',SURVYEAR)as NSS_SURVYEAR,

if(Left(SURVYEAR,3)='SES',SURVYEAR)as SES_SURVYEAR

and after that drop field 'SURVYEAR'

This will remove Synthetic Key

I made this change in your inline table SURVYEAR as

Load SURVYRORD,

if(Left(SURVYEAR,3)='NSS',SURVYEAR)as NSS_SURVYEAR,

if(Left(SURVYEAR,3)='SES',SURVYEAR)as SES_SURVYEAR

Resident SURVEYYEAR;

DROP Field SURVYEAR;

It will solve your problem.

Regards,