Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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] ;
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] ;
Thank You very much Mr.Wassenaar it really helpful for me