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
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;
Subjects 13/14 and Subject 14/15 are two fields in your database? May be look at CrossTable LOAD here: The Crosstable Load
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
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
May be this:
Table:
CrossTable (Subject, Data, 3)
LOAD UniqueKey,
Year,
University,
Subject 12012/13,
Subject 12013/14
FROM sourceFile;
Update: Sample attached
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];
or
Table:
Load UniqueKey,
Year,
University,
[Subjects 2013/14] as Subject
Resident...
concatenate
Load
UniqueKey,
Year,
University,
[Subject 2014/15] as Subject
Resident....
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;
I agree with neetha P - just concatenate the data and rename fields...