Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

pandiarajan
Contributor

limiter the rows

Dear Friends,

    Table I   

RegionRegionMail IDBranchBranchMailiDSubbranchSUBBRANCHMAILID
M1M1@gmail.comB1B1@gmail.comS1B1S1B1@gmail.com
M1M1@gmail.comB1B1@gmail.comS2B1S2B1@gmail.com
M1M1@gmail.comB1B1@gmail.comS3B1S3B1@gmail.com
M1M1@gmail.comB2b2@gmail.comS1B2S1B2@gmail.com
M1M1@gmail.comB2b2@gmail.comS2B2S2B2@gmail.com

Table II

RegionReginTotalID
M1M1@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

1 Solution

Accepted Solutions

Re: limiter the rows

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] ;


talk is cheap, supply exceeds demand
2 Replies

Re: limiter the rows

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] ;


talk is cheap, supply exceeds demand
pandiarajan
Contributor

Re: limiter the rows

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

Community Browser