topic Merge columns from Excel source in QlikView

Merge columns from Excel source

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Merge columns from Excel source

Kushal_Chawda — Thu, 26 May 2016 15:19:18 GMT

Data:

LOAD Date,

alt( [Monday Notes],[Tuesday Notes],[Wednesday Notes]) as Notes,

Hour

FROM Table

Re: Merge columns from Excel source

Kushal_Chawda — Thu, 26 May 2016 15:26:00 GMT

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;

Re: Merge columns from Excel source

sunny_talwar — Thu, 26 May 2016 16:23:50 GMT

I like this method but with a slight modification:

Data:

LOAD Date,

RangeMaxString([Monday Notes], [Tuesday Notes], [Wednesday Notes]) as Notes,

Hour

FROM Table;

Re: Merge columns from Excel source

tamilarasu — Thu, 26 May 2016 17:18:40 GMT

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

Re: Merge columns from Excel source

Kushal_Chawda — Mon, 30 May 2016 11:56:27 GMT

trick acchi he..