how to merge 2 rows in a data file?

[sujana1621]

Hi,

I have data which is split into 2 rows.

For eg: A person is asked Question00 and the corresponding response is recorded. For the same question, additional details was also

asked which is recorded in a separate field which is Text and recorded in next row. What I need to show is as below for Question00

Questions	Response	Text	OrderNo
Question00	Sometimes	could not continue a while back.	00

transf:

LOAD Questions,

     concat(Text,'/') as Text,

     OrderNo

FROM example.xlsx  (biff, embedded labels, table is Sheet1$)  group by OrderNo, Questions, Response;

But this is not working.

Thanks

Sujana

[Nicole-Smith]

Using your example file, load script like this:

Data:
LOAD SUBFIELD(SUBFIELD(Questions, ' ', 1), '?', 1) AS Questions, 
     CONCAT(Response, ', ') AS Response, 
     CONCAT(Text, ',') AS Text, 
     OrderNo
FROM example.xlsx (ooxml, embedded labels, table is Sheet1)
GROUP BY SUBFIELD(SUBFIELD(Questions, ' ', 1), '?', 1), OrderNo;

Returns data that looks like this:

Questions	Response	Text	OrderNo
Question00	Sometimes	could not continue a while back.	0
Question1	Yes		8
Question02	Does not diagnose	Not something i look for previously	2
Question04	Anti-inflammatory		4
Question06	Other	thinks there’s a mechanistic aspect that can cause it.	6
Question08		There’s some study evidence out there.	8
Question10	Depends on event	can’t tolerate it.	10
Question12	Yes		12
Question14	<5%		14
Question16	Yes		16

[sujana1621]

Thank You Nicole Smith. It works now.