Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with field concatenation during load


Hi,

I have a table with sample data as follows:

                                                            

OPT_VGUIDCHARCVLC_VALUE
ASD_D000006023
ASD_D050002828
ASD_M000000090
BSD_D700002309
BSD_D710003823
BSD_J350006634
BSD_JAA0002647
BSD_JDN0006695
BSD_JER2001484
BSD_LNA0014632

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

               

OPT_VGUIDVLC_VALUE
A06023 - 02828 - 00090
B02309 - 03823 - 06634 - 02647 - 06695 - 01484 - 14632

is this possible?

3 Replies
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;

Not applicable
Author

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_VGUIDNEW_VLC_VALUE
A02309 - 02647 - 03823
  - 78817
B00131 - 00632 - 01470
  - 01526 - 02116 - 02415 - 02817 - 06312 - 08297
C00131 - 00632 - 01074
  - 01470 - 01526 - 01557 - 02415 - 02817 - 06312
D00131 - 00632 - 01470
  - 01526 - 02116 - 02415 - 02817 - 06312 - 06546 - 08297

thanks for your help!

jagan
Luminary Alumni
Luminary Alumni

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.