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

[mattphillip]

Sorry had // them out as I thought it was initially causing the error. Just tried putting it back in and got exactly the same error.

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';

LOAD UniqueKey,

     Year,

     Unis,

     [JACS-A],

     [JACS-B]

FROM

(ooxml, embedded labels, table is Sheet1);

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]

You have not named Intial table.

Try:

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;

[mattphillip]

Good news, no errors. However, please see below. Is there anyway to do this and no create the blank entries?

[sunny_talwar]

Try this:

Table:

CrossTable (Subject, Data, 3)

LOAD UniqueKey,

          Year,

          University,

          Subject 12012/13,

          Subject 12013/14

FROM sourceFile;

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Data)) > 0;

DROP Table Table;

[mattphillip]

Thanks for this Sunny. It's not quite what I had in mind though.

I was looking for the values to be combined into a single field like below:

UniqueKey	Year	University	JACS
1	2012/13	Uni1	Computing
2	2012/13	Uni2	Medicine
3	2012/13	Uni3	Art
4	2012/13	Uni4	History
5	2013/14	Uni1	Food
6	2013/14	Uni2	Accounting
7	2013/14	Uni3	English
8	2013/14	Uni4	Geography

Any idea whether this can be done?

Thanks!

[shambhub]

Hi Matt,

Try like this you will get it.

Load UniqueKey,

     Year,

     University,

     [Subject 2012/13],

     [Subject 2013/14],

     If(IsNull([Subject 2012/13]),[Subject 2013/14],[Subject 2012/13]) as Subject;

LOAD UniqueKey,

     Year,

     University,

     [Subject 2012/13],

     [Subject 2013/14]

FROM

(ooxml, embedded labels, table is Sheet1);

Best Regards

Shambhu B

[sunny_talwar · Accepted solution]

This is what I gave you:

Script:

Table:

CrossTable (Subject, Data, 3)

LOAD UniqueKey,

    Year,

    University,

    [Subject 2012/13],

    [Subject 2013/14]

FROM

[https://community.qlik.com/thread/186661]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Len(Trim(Data)) > 0;

DROP Table Table;

[mattphillip]

My apologies Sunny, didn't realise I hadn't added the new 'Data' field to my table.

Many thanks.

[mattphillip]

Hi Sunny, just one final question - in the line 'CrossTable (JACS,Data,3)' what does the 3 refer to?

[sunny_talwar]

3 means that start the CrossTable starting with field 4 and keep the first 3 fields as columns.