Merging two fields together

[mattphillip]

Hi, I'm hoping someone can help. I have a large dataset, and it includes two columns with similar values (academic  subjects - they cover different years with the second being a slightly revised list). I'm looking for a way to combine these two field ('Subjects 13/14' and 'Subjects 14/15') into a single 'Subject' field whilst keeping their respective values.

Is there any way to do this?

Appreciated.

Matt

[nizamsha]

Hi

That was a good option too but when comes to next year you will get new column say 2015/2016 but at that time what will u do, will u concatenate and rename the field  again,instead of that in crosstable  u can add the column only enough

[mattphillip]

Thanks for this, but like when I tried to combine the columns myself, you end up with blank values through the dataset every other row which would turn my 12.5 million rows into 25 million rows.

[mattphillip]

Thanks Neetha. Looks like it should work. Can you confirm how it should be written within the script. When I attempted it, I'm getting error messages. Thanks!

Table:

Load UniqueKey,

Year,

University,

[Subjects 2013/14] as Subject

Resident Table;

concatenate

Load

UniqueKey,

Year,

University,

[Subject 2014/15] as Subject

Resident Table;

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

Hi Matt,

Please post the script with error you are getting.

Regards

Neetha

[mattphillip]

The JACS-A, JACS-B fields are the Subject 2012/13 and Subject 2013/14 respectively and JACS is Subject noted in the example.

Any ideas?

[Anonymous]

Hi Matt,

Please try by adding noconcatenate:

Table:

Noconcatenate

Load UniqueKey,

Year,

University,

[Subjects 2013/14] as Subject

Resident...

concatenate

Load

UniqueKey,

Year,

University,

[Subject 2014/15] as Subject

Resident....

[mattphillip]

Still no luck. Same error messages (but now with Noconcatenate added to the first).

[Anonymous]

Please post whole script.

[mattphillip]

See below for the script. I've created a small test file to try out the solution to save having all the other non-important fields in there:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

Noconcatenate

Load UniqueKey,

Year,

University,

[JACS-A] as JACS

Resident Table;

concatenate

Load

UniqueKey,

Year,

University,

[JACS-B] as JACS

Resident Table;

FROM

(ooxml, embedded labels, table is Sheet1);

[Anonymous]

Hi Matt,

Where is intial load?

May be:

TableA:

Load

UniqueKey,

Year,

University,

[JACS-A],

[JACS-B]

FROM

(ooxml, embedded labels, table is Sheet1);

Table:

Noconcatenate

Load UniqueKey,

Year,

University,

[JACS-A] as JACS

Resident TableA;

concatenate

Load

UniqueKey,

Year,

University,

[JACS-B] as JACS

Resident TableA;