Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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);
Hi Matt,
Please post the script with error you are getting.
Regards
Neetha
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?
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....
Still no luck. Same error messages (but now with Noconcatenate added to the first).
Please post whole script.
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);
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;