topic Re: merge rows in QlikView

merge rows

Mon, 06 Apr 2015 21:21:20 GMT

I have the below table

Name	3-support	onemore

a	table2-support3	er
n	e	rt
c	y	ty
d		no
d	oh

I want to merge the rows based on the Name for e.g.

The last 2 rows should be merged in one row as

d oh no

Can someone please help understand how I can accomplish this? I tried doing a self join but that didn't get me the results I want. Thanks in advance for your help.

Thanks,
Samira

Re: merge rows

Anonymous — Mon, 06 Apr 2015 21:31:22 GMT

based on your example, (assuming there is value only in either of 3-support/onemore fields )

1. first load distinct Name in a Table1 from from your table

2. left join Table 1, load Name, 3-support from your table where len(trim(3-support))>=1

2. left join Table 1, load Name, onemore from your table where len(trim(onemore))>=1

Re: merge rows

Anonymous — Mon, 06 Apr 2015 21:33:36 GMT

Please use distinct on all loads

Re: merge rows

maxgro — Mon, 06 Apr 2015 22:39:45 GMT

try group by name

source:

load * inline [

Name, 3-support, onemore

a, table2-support3, er

n, e, rt

c, y, ty

d,,no

d, oh,

];

final:

NoConcatenate load

Name, MaxString([3-support]) as [3-support], MaxString(onemore) as onemore

Resident source

group by Name;

DROP Table source;

Re: merge rows

Tue, 07 Apr 2015 15:29:47 GMT

Thank you for your response. This is what I tried:

join1:

LOAD DISTINCT Name,

[3-support],

onemore

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join(join1)

LOAD DISTINCT Name,

[3-support]

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1)where len(trim([3-support]))>=1;

left join (join1)

LOAD DISTINCT Name,

onemore

FROM

[..\Temp\joinTest2.xlsx]

(ooxml, embedded labels, table is Sheet1)where len(trim(onemore))>=1;

So if I pull the rows into a table box, should I now see only 1 row replacing the 2 rows?

If so then I don't see that happening. Trying to figure out what I am missing. Your guidance is much appreciated.

Thanks,

Samira

Re: merge rows

Tue, 07 Apr 2015 15:42:33 GMT

Thanks Massimo.

That worked!

Re: merge rows

maxgro — Tue, 07 Apr 2015 16:06:19 GMT

please mark the helpful and correct answers (to close the discussion), thanks

Re: merge rows

Tue, 07 Apr 2015 16:08:47 GMT

Surely will. I am trying to apply the same solution to a larger data set of approx 60K rows that I need to create the unique rows for and am running into errors there. Hence the delay in marking the answer as correct.

Re: merge rows

Tue, 07 Apr 2015 16:23:21 GMT

I have a further question:

For my actual datasheet, I am doing a if(Match) during the load to load entries from one column into different new columns based on a condition. For e.g.:

if(Match([role],'First Call Support Contact'),[full name]) as [First Call Support Contact Full Name],

if(Match([role],'First Call Support Contact'),eMail) as [First Call Support Contact Email]

so if I drop the source table I lose that data. How can I address that issue?

Thanks,

Samira

Re: merge rows

maxgro — Tue, 07 Apr 2015 16:45:06 GMT

could you open a new discussion with some data?

I don't understand your last question.

maybe

.....

if(role = 'First Call Support Contact', [full name]) as [First Call Support Contact Full Name],

.....

Re: merge rows

Tue, 07 Apr 2015 16:53:53 GMT

Going back to the example you used for the solution, you did a load inline for source table. I am not doing a load inline but instead am loading from a spreadsheet. And during the load I am doing a if(match) to get data into separate columns. so based on your solution, if I drop the source table at the end, won't I lose the new columns that I am creating during my initial load of the source table?

Sorry if this is getting complicated. I will start a new discussing but it will be an extension of this post.Thanks for all your help.

Thanks,

Samira