Concate all fields to a single field excluding null values

[Gopi_E]

Hi all,

I have a data like this

SNO	A	B	C	D	E	F	G	H	I	J
1	1.4582	1.235	54.415
2	1.4582	1.8858	54.285	4.4555	2.224	1.225	3.2354	3.465	6.3255	1.4536
3	2.3453	2.423 4.454	3.4543	2.44545	3.576	2.2545	4.3243	2.536	6.341	1.635
4	1.4578
5						4.24255
6			3.42546			344			344
7	2544	2233.1	222.2	22

After concate the result as below:

Here how to avoid repeated ',' here some fields having null values:

expression was which is i used : A&','&B&','&C&','&D&','&E&','&F&','&G&','&H&','&I&','&J

I want the only single comma between two values, please suggest

Thanks in advance

Regards,

Gopi

[Gopi_E]

Hi Sunny can you look on to this

stalwar1‌

Thanks in advance

[marcus_sommer]

There are various ways possible. The easiest is probably to wrap your field-concat with multiple replace-statements like:

replace(replace(A&','&B&','&C&','&D&','&E&','&F&','&G&','&H&','&I&','&J, ',,', ','), ',,,', ',')

Also possible is to replace the commas with a mapsubstring() or loading your data with a crosstable and concat() it result again. Both approaches will need additionally load-statements but they will be more handy as the replace-chain if you hadn't just a few fields else many of them.

- Marcus

[Gopi_E]

Hi Marcus,

Thanks for reply

can you elaborate crosstable approach

I tried by using crosstable but it didn't work for me can you suggest

FieldsAddData:

CrossTable(attr,Data,10)

LOAD SNO,

     A,

     B,

     C,

     D,

     E,

     F,

     G,

     H,

     I,

     J,

FROM

[..\..\Vinod SampleData\AddAllFieldsData.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(FieldsAddData)

LOAD Distinct

SNO,

Concat(Data,',') as AD

Resident FieldsAddData Where Len(Data)>0 Group By SNO;

Drop Fields attr,Data;

is there any mistake here.

[vishsaggi · Accepted solution]

One way may be using Cross Table try like

PivotEg:

CrossTable(DataField, Value)

LOAD SNO,

     A,

     B,

     C,

     D,

     E,

     F,

     G,

     H,

     I,

     J

FROM

[..\Desktop\Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

[marcus_sommer]

In general it looked ok. but your third parameter within the crosstable is 10 and it should be 1 because you have just one leading field.

- Marcus

[Gopi_E]

Hi Vishwarath,

Thanks for the reply, Now correct my crosstable approach and its working now.

Regards,

Gopi

[Gopi_E]

Hi Marcus,

Thanks for the reply,

I corrected my crosstable third argument.

Now it is working

Regards,

Gopi