Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to merge columns in the load script from my Excel Source.
The table looks like this:
Date | Monday notes | Tuesday notes | Wednesday notes | Hours |
---|---|---|---|---|
01.01.2016 | Meeting 1 | 1 | ||
02.01.2016 | Meeting 2 | 1 | ||
03.01.2016 | Meeting 3 | 1 |
I want to merge the Monday/Tuesday/Wednesday notes columns into a New column named notes:
Date | Monday notes | Tuesday notes | Wednesday notes | Hours | Notes |
---|---|---|---|---|---|
01.01.2016 | Meeting 1 | 1 | Meeting 1 | ||
02.01.2016 | Meeting 2 | 1 | Meeting 2 | ||
03.01.2016 | Meeting 3 | 1 | Meeting 3 |
How can I do this?
Thanks!
You can also try,
Data:
LOAD Date,
[Monday Notes] & [Tuesday Notes] & [Wednesday Notes] as Notes,
Hour
FROM Table;
If the columns have any spaces, you can use trim function like below.
Trim([Monday Notes] & [Tuesday Notes] & [Wednesday Notes]) as Notes
Data:
LOAD Date,
alt( [Monday Notes],[Tuesday Notes],[Wednesday Notes]) as Notes,
Hour
FROM Table
or
Data:
CrossTable(Day,Notes,2)
LOAD Date,
Hours,
[Monday notes],
[Tuesday notes],
[Wednesday notes]
FROM
[https://community.qlik.com/thread/218440]
(html, codepage is 1252, embedded labels, table is @1);
Final:
NoConcatenate
LOAD *
Resident Data
Where len(trim(Notes))<>0;
DROP Table Data;
I like this method but with a slight modification:
Data:
LOAD Date,
RangeMaxString([Monday Notes], [Tuesday Notes], [Wednesday Notes]) as Notes,
Hour
FROM Table;
You can also try,
Data:
LOAD Date,
[Monday Notes] & [Tuesday Notes] & [Wednesday Notes] as Notes,
Hour
FROM Table;
If the columns have any spaces, you can use trim function like below.
Trim([Monday Notes] & [Tuesday Notes] & [Wednesday Notes]) as Notes
trick acchi he..