topic Re: How do I merge the rows? in QlikView

How do I merge the rows?

Jens — Tue, 19 Oct 2021 09:22:34 GMT

Hey,

I have the following table:

Person	date	time1	time2
bob	05-10-2021	2 min	-
bob	05-10-2021	-	5 min
roy	05-10-2021	1 min	-
roy	05-10-2021	-	6 min

Maybe you see the problem.. I want to have the data in one row and not in the displayed version. My result should look like these:

Person	date	time1	time2
bob	05-10-2021	2 min	5 min
roy	05-10-2021	1 min	6 min

I was trying lots of joins and concatenation functions, but nothing worked. I hope you can help me.

Re: How do I merge the rows?

AshutoshBhumkar — Tue, 19 Oct 2021 10:04:33 GMT

Hello,

This happens when you concatenate tables.

Try to find the key field in the table and then try joining time2 data on the key.

You may need to create a composite key field with the combination of Person & Date (Not sure about your exact data).

Thanks,
Ashutosh

Re: How do I merge the rows?

Jens — Tue, 19 Oct 2021 10:15:06 GMT

You mean creating a synthetic KEY?

Re: How do I merge the rows?

AshutoshBhumkar — Tue, 19 Oct 2021 10:18:18 GMT

No.

Composite Keys are combination of two columns to create uniqueness.

E.g Person&'-'&Date would be BOB-5-10-2021

Thanks,

Ashutosh

Re: How do I merge the rows?

Jens — Tue, 19 Oct 2021 10:21:12 GMT

Okay, I understand.. but this would crash my analytics process after, if I can't filter through the name and the date.

Re: How do I merge the rows?

AshutoshBhumkar — Tue, 19 Oct 2021 10:24:38 GMT

Create a different field for Composite key which would not be the part of selections on Sheet.

E.g

Load

Category,

Date

Values

Category&'-'&Date as KeyForTime2

Re: How do I merge the rows?

abhijitnalekar — Tue, 19 Oct 2021 10:25:18 GMT

Hi @Jens ,

Try below script

Data:
LOAD
Person,
"date",
time1,
time2
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is Sheet7);

Time1:
Load
Person&"date" as Key,
Person,
"date",
time1
resident Data
where time1<>'-';

Time1:
Load
Person&"date" as Key,
time2
resident Data
where time2<>'-';
Drop table Data;

Re: How do I merge the rows?

Jens — Tue, 19 Oct 2021 10:51:14 GMT

This isn't working.. the same problem as before

Re: How do I merge the rows?

abhijitnalekar — Tue, 19 Oct 2021 11:08:28 GMT

Hi @Jens ,

Strange. The same code is working at my side.

Please find attached QVF for reference.

Re: How do I merge the rows?

Jens — Tue, 19 Oct 2021 12:36:48 GMT

I've tried your solution. The values time1 and time2 are joined, but the old values ('-') exist futhermore. How can I delete them? @AshutoshBhumkar

Re: How do I merge the rows?

AshutoshBhumkar — Tue, 19 Oct 2021 15:11:51 GMT

Hello,

Could you provide any screenshot for those '-'.

We can remove those, If those are not relevant

Re: How do I merge the rows?

Jens — Tue, 19 Oct 2021 15:21:58 GMT

@AshutoshBhumkar This is how my table looks like now:

You see that Qlik has joined the attribute time1 and time 2 in row2, but the rows with only time1 and time2 are preserved.

Re: How do I merge the rows?

AshutoshBhumkar — Tue, 19 Oct 2021 16:06:50 GMT

Maybe you need to remove null values and then join.

Re: How do I merge the rows?

Jens — Wed, 20 Oct 2021 05:09:11 GMT

Yes.. but if I do it, then the values other time values get deleted. I don't understand why the join is so strange. Normally in a SQL Join there are only the values, that could have been joined together, but in Qlik there is somehow a other comphrehension of a join..