How do I merge the rows?

[Jens]

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.

[AshutoshBhumkar]

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

[Jens]

You mean creating a synthetic KEY?

[AshutoshBhumkar]

No.

Composite Keys are combination of two columns to create uniqueness.

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

Thanks,

Ashutosh

[Jens]

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

[AshutoshBhumkar · Accepted solution]

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

[abhijitnalekar]

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;

[Jens]

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

[abhijitnalekar]

Hi @Jens ,

Strange. The same code is working at my side.

Please find attached QVF for reference.

[Jens]

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