Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Contributor

Merging two fields together

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

1 Solution

Accepted Solutions
Highlighted

Re: Merging two fields together

This is what I gave you:

Capture.PNG

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;

View solution in original post

33 Replies
Highlighted

Re: Merging two fields together

Subjects 13/14 and Subject 14/15 are two fields in your database? May be look at CrossTable LOAD here: The Crosstable Load

Highlighted
Valued Contributor

Re: Merging two fields together

Hi,

Maybe this helps

IntervalMatch

Regards.

Highlighted
Valued Contributor II

Re: Merging two fields together

Hi

YA Sunny is correct if u want the two date to be in the same field then go for cross table if u want to replace the old one and keep the new one mean go for mapping

try to upload some sample data so u will get quick response

Highlighted
Contributor

Re: Merging two fields together

UniqueKeyYearUniversitySubject 2012/13Subject 2013/14
12013/14Uni1Computing
22013/14Uni2Medicine
32013/14Uni3Art
42013/14Uni4History
52012/13Uni1Food
62012/13Uni2Accounting
72012/13Uni3English
82012/13Uni4Geography

See above. I'd like to create the same table but with the final two columns combined as simply 'Subject'.

Matt

Highlighted

Re: Merging two fields together

May be this:

Table:

CrossTable (Subject, Data, 3)

LOAD UniqueKey,

          Year,

          University,

          Subject 12012/13,

          Subject 12013/14

FROM sourceFile;

Update: Sample attached

Highlighted
Honored Contributor

Re: Merging two fields together

Hi Matt,

Rename fields and drop fields [Subjects 2013/14] and [Subjects 2014/15]

Table:

Load *,

[Subjects 2013/14] as Subject

Resident...

concatenate

Load *,

[Subject 2014/15] as Subject

Resident....


Drop field [Subjects 2013/14];

Drop field [Subjects 2014/15];

Highlighted
Honored Contributor

Re: Merging two fields together

or

Table:

Load UniqueKey,

Year,

University,

[Subjects 2013/14] as Subject

Resident...


concatenate


Load

UniqueKey,

Year,

University,

[Subject 2014/15] as Subject

Resident....


Highlighted
Valued Contributor II

Re: Merging two fields together

TableA:

LOAD * Inline [

ID,Year,University,Year1,Year2

1,12/14,AA,Text

2,13/14,AA,  ,Text2

];

CrossTable (one,Two,3)

LOAD

ID,Year,University,Year1,Year2      Resident TableA;

drop Table TableA;

Highlighted
Valued Contributor

Re: Merging two fields together

I agree with neetha P - just concatenate the data and rename fields...