Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Concatenate to Pipe Delimited Fields and Maintain Order

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

UserIDBPNCustomer #Sub Date
123100888400123

11/07/2013

12310059940045608/07/2014
12310063740078911/07/2013
12310055240032122/06/2016
12310055240065408/07/2014
12310079240098727/10/2016
12310059940015903/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

UserIDBPNCustomer #Sub Date
123100888|100599|100637|100552|100552|100792|100599400123|400456|400789|400321|400654|400987|40015911/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

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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

View solution in original post

11 Replies
Anil_Babu_Samineni

May be look Concat - script function ‒ QlikView

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
haymarketpaul
Creator III
Creator III
Author

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

Clever_Anjos
Employee
Employee

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

Capture.PNG

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
haymarketpaul
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thanks for reply

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

Clever_Anjos
Employee
Employee

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
haymarketpaul
Creator III
Creator III
Author

Perfect - thank you