limiter the rows

[pandiarajan]

Dear Friends,

    Table I   

Region	RegionMail ID	Branch	BranchMailiD	Subbranch	SUBBRANCHMAILID
M1	M1@gmail.com	B1	B1@gmail.com	S1B1	S1B1@gmail.com
M1	M1@gmail.com	B1	B1@gmail.com	S2B1	S2B1@gmail.com
M1	M1@gmail.com	B1	B1@gmail.com	S3B1	S3B1@gmail.com
M1	M1@gmail.com	B2	b2@gmail.com	S1B2	S1B2@gmail.com
M1	M1@gmail.com	B2	b2@gmail.com	S2B2	S2B2@gmail.com

Table II

Region	ReginTotalID
M1	M1@gmail.com;B1@gmail.com;B2@GMAIL.COM;S1B1@gmail.com;S2B1@gmail.com;S3B1@gmail.com;S1B2@gmail.com;S...

i have data like Table I based on that i need Table II .  i want to avoid the left join. anybody suggest any functions or other methods

[Gysbert_Wassenaar · Accepted solution]

Try something like this:

LOAD Region, [RegionMail ID] & ';' & Concat([BranchMailiD],';') & ';' & concat(SUBBRANCHMAILID,';') as ReginTotalID

group by Region,[RegionMail ID] ;

LOAD * INLINE [

    Region, RegionMail ID, Branch, BranchMailiD, Subbranch, SUBBRANCHMAILID

    M1, M1@gmail.com, B1, B1@gmail.com, S1B1, S1B1@gmail.com

    M1, M1@gmail.com, B1, B1@gmail.com, S2B1, S2B1@gmail.com

    M1, M1@gmail.com, B1, B1@gmail.com, S3B1, S3B1@gmail.com

    M1, M1@gmail.com, B2, b2@gmail.com, S1B2, S1B2@gmail.com

    M1, M1@gmail.com, B2, b2@gmail.com, S2B2, S2B2@gmail.com

];

The inline load is just an example. Replace it with the load statement that loads your data. If you want to keep the orginal data too then don't use the preceding load but a resident load:

T1:

LOAD * INLINE [

    Region, RegionMail ID, Branch, BranchMailiD, Subbranch, SUBBRANCHMAILID

    M1, M1@gmail.com, B1, B1@gmail.com, S1B1, S1B1@gmail.com

    M1, M1@gmail.com, B1, B1@gmail.com, S2B1, S2B1@gmail.com

    M1, M1@gmail.com, B1, B1@gmail.com, S3B1, S3B1@gmail.com

    M1, M1@gmail.com, B2, b2@gmail.com, S1B2, S1B2@gmail.com

    M1, M1@gmail.com, B2, b2@gmail.com, S2B2, S2B2@gmail.com

];

T2:

LOAD Region, [RegionMail ID] & ';' & Concat([BranchMailiD],';') & ';' & concat(SUBBRANCHMAILID,';') as ReginTotalID

resident T1

group by Region,[RegionMail ID] ;

[pandiarajan]

Thank You very much Mr.Wassenaar it really helpful for me