Help with field concatenation during load

[#1]

Hi,

I have a table with sample data as follows:

OPT_VGUID	CHARC	VLC_VALUE
A	SD_D0000	06023
A	SD_D0500	02828
A	SD_M0000	00090
B	SD_D7000	02309
B	SD_D7100	03823
B	SD_J3500	06634
B	SD_JAA00	02647
B	SD_JDN00	06695
B	SD_JER20	01484
B	SD_LNA00	14632

during the load I need the VLC_VALUE field to concatenate based on each unique OPT_VGUID, so the result is:

OPT_VGUID	VLC_VALUE
A	06023 - 02828 - 00090
B	02309 - 03823 - 06634 - 02647 - 06695 - 01484 - 14632

is this possible?

[sunny_talwar]

Try this:

Table:

LOAD OPT_VGUID,

          CHARC,

          VLC_VALUE,

          RowNo() as Sort

FROM Source;

NewTable:

LOAD OPT_VGUID

          Concat(VLC_VALUE, ' - ', Sort) as VLC_VALUE

Resident Table

Group By OPT_VGUID;

DROP Table Table;

[#3]

Thanks Sunny

I found this wasn't working with the 'Sort' concatenation as you have provided.

so I amended as follows - 

TABLE_1:
LOAD  OPT_VGUID,

        CHARC,
      VLC_VALUE,
      RowNo() as OPT_Sort
FROM
SOURCE;

NEW_TABLE:
LOAD OPT_VGUID,
     Concat(VLC_VALUE, ' - ') as NEW_VLC_VALUE
Resident TABLE
group by VGUID;

DROP table TABLE_1;

This gave the required results and works perfectly!

OPT_VGUID	NEW_VLC_VALUE
A	02309 - 02647 - 03823   - 78817
B	00131 - 00632 - 01470   - 01526 - 02116 - 02415 - 02817 - 06312 - 08297
C	00131 - 00632 - 01074   - 01470 - 01526 - 01557 - 02415 - 02817 - 06312
D	00131 - 00632 - 01470   - 01526 - 02116 - 02415 - 02817 - 06312 - 06546 - 08297

thanks for your help!

[jagan]

Hi,

Try like this

Data:

LOAD OPT_VGUID

          Concat(VLC_VALUE, ' - ') AS VLC_VALUE

GROUP BYOPT_VGUID;

LOAD

*

FROM DataSource;

Hope this helps you.

Regards,

Jagan.