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
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);
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;
Good news, no errors. However, please see below. Is there anyway to do this and no create the blank entries?
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;
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!
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
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;
My apologies Sunny, didn't realise I hadn't added the new 'Data' field to my table.
Many thanks.
Hi Sunny, just one final question - in the line 'CrossTable (JACS,Data,3)' what does the 3 refer to?
3 means that start the CrossTable starting with field 4 and keep the first 3 fields as columns.