topic Re: Merging two fields together in QlikView

Merging two fields together

mattphillip — Tue, 13 Oct 2015 14:34:58 GMT

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

Re: Merging two fields together

sunny_talwar — Tue, 13 Oct 2015 14:40:21 GMT

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

Re: Merging two fields together

Anonymous — Tue, 13 Oct 2015 14:51:40 GMT

Hi,

Maybe this helps

IntervalMatch

Regards.

Re: Merging two fields together

nizamsha — Tue, 13 Oct 2015 14:54:20 GMT

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

Re: Merging two fields together

mattphillip — Tue, 13 Oct 2015 15:03:09 GMT

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

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

Matt

Re: Merging two fields together

sunny_talwar — Tue, 13 Oct 2015 15:05:49 GMT

May be this:

Table:

CrossTable (Subject, Data, 3)

LOAD UniqueKey,

Year,

University,

Subject 12012/13,

Subject 12013/14

FROM sourceFile;

Update: Sample attached

Re: Merging two fields together

Anonymous — Tue, 13 Oct 2015 15:09:22 GMT

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

Re: Merging two fields together

Anonymous — Tue, 13 Oct 2015 15:12:54 GMT

Table:

Load UniqueKey,

Year,

University,

[Subjects 2013/14] as Subject

Resident...

concatenate

Load

UniqueKey,

Year,

University,

[Subject 2014/15] as Subject

Resident....

Re: Merging two fields together

nizamsha — Tue, 13 Oct 2015 15:15:17 GMT

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;

Re: Merging two fields together

juleshartley — Tue, 13 Oct 2015 15:31:20 GMT

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

Re: Merging two fields together

nizamsha — Tue, 13 Oct 2015 16:10:28 GMT

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

Re: Merging two fields together

mattphillip — Wed, 14 Oct 2015 07:44:42 GMT

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.

Re: Merging two fields together

mattphillip — Wed, 14 Oct 2015 07:57:52 GMT

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

Re: Merging two fields together

Anonymous — Wed, 14 Oct 2015 08:19:18 GMT

Hi Matt,

Please post the script with error you are getting.

Regards

Neetha

Re: Merging two fields together

mattphillip — Wed, 14 Oct 2015 08:24:47 GMT

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?

Re: Merging two fields together

Anonymous — Wed, 14 Oct 2015 08:28:56 GMT

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....

Re: Merging two fields together

mattphillip — Wed, 14 Oct 2015 08:34:38 GMT

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

Re: Merging two fields together

Anonymous — Wed, 14 Oct 2015 08:39:37 GMT

Please post whole script.

Re: Merging two fields together

mattphillip — Wed, 14 Oct 2015 08:43:15 GMT

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

Re: Merging two fields together

Anonymous — Wed, 14 Oct 2015 08:44:58 GMT

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;