Concatenate to Pipe Delimited Fields and Maintain Order

[haymarketpaul]

QV12 SR 4

Hoping someone can point me in the right direction.

I need to concatenate some answers into a single row whereby some fields will be pipe delimited but i need to maintain a certain order in the pipe delimited fields.

Best explained with an example i think.  Let's say i have this data.  NB  Customer # is always unique per user all others may not be

UserID	BPN	Customer #	Sub Date
123	100888	400123	11/07/2013
123	100599	400456	08/07/2014
123	100637	400789	11/07/2013
123	100552	400321	22/06/2016
123	100552	400654	08/07/2014
123	100792	400987	27/10/2016
123	100599	400159	03/06/2015

So i need to end up with one row per user - so the BPN, Customer # and Sub Date fields would be pipe delimited as follows

UserID	BPN	Customer #	Sub Date
123	100888|100599|100637|100552|100552|100792|100599	400123|400456|400789|400321|400654|400987|400159	11/07/2013|08/07/2014|11/07/2013|22/06/2016|08/07/2014|27/10/2016|03/06/2015

So in the above result the first pipe in each field corresponds to the first row from the top table and the second pipe corresponds to the second row and so on for however many rows per user.

It doesn't matter by which column(s) the top table is initially ordered.

Thought i might be able to use Concat with a weighted sort but haven't managed to crack it yet and may be over thinking the issue!?

Hope that makes sense - any ideas greatly welcomed

[Anil_Babu_Samineni]

May be look Concat - script function ‒ QlikView

[haymarketpaul]

Yes i'm very familiar with that function as i mention but am still struggling to get it working as it should

[Clever_Anjos]

Maybe

Load

     UserID

    Concat(BPN,'|',BPN) as BPN2,

    Concat([Customer #],'|',BPN) as [Customer #2]

resident yourtable

Group by UserID

[Anil_Babu_Samineni]

Looks like working

Concat:

LOAD * Inline [

UserID, BPN, Customer #, Sub Date

123, 100888, 400123, 11/07/2013

123, 100599, 400456, 08/07/2014

123, 100637, 400789, 11/07/2013

123, 100552, 400321, 22/06/2016

123, 100552, 400654, 08/07/2014

123, 100792, 400987, 27/10/2016

123, 100599, 400159, 03/06/2015

];

Concat1:

NoConcatenate

LOAD UserID,Concat(BPN,'|') as BPN, Concat([Customer #],'|') as [Customer #], Concat([Sub Date],'|') as [Sub Date]  Resident Concat Group By UserID;

DROP Table Concat;

[haymarketpaul]

Not sure that's working ok i'm afraid

Assuming the first pipe portion in BPN is 100888 then the first pipe portion in the Customer # field would be 400123 and the first pipe portion in the Sub Date field would be 11/07/2013

Your ones seem to be random - it's really important that the sequence is maintained as the resultant table will be loaded into another system and it will need to make sense in there

Thanks for trying

[haymarketpaul]

Thanks for reply

Doesn't seem to work if more than one BPN number is the same

[Clever_Anjos · Accepted solution]

Concat:

LOAD *, recno() as order Inline [

UserID, BPN, Customer #, Sub Date

123, 100888, 400123, 11/07/2013

123, 100599, 400456, 08/07/2014

123, 100637, 400789, 11/07/2013

123, 100552, 400321, 22/06/2016

123, 100552, 400654, 08/07/2014

123, 100792, 400987, 27/10/2016

123, 100599, 400159, 03/06/2015

];

Load

     UserID

    Concat(BPN,'|',order) as BPN2,

    Concat([Customer #],'|',order) as [Customer #2]

resident yourtable

Group by UserID

[Anil_Babu_Samineni]

Interesting ... cleveranjos‌ will help us

[haymarketpaul]

Perfect - thank you