Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to Concatenate two Columns with Expression Editor

Hi, 

Two questions actually.  The first is can I concatenate two columns with Expression Editor?  Here's the data below:

Orders:
LOAD * INLINE [
Customer, OrderID, Product, Cost
1, 201, Chain, 20
1, 201, Seat, 40
1, 201, Mudguard, 50
2, 202, Gloves, 15
2, 202, Basket, 60
3, 203, Helmet, 70
];



Can I make a column called Customer_OrderID with the following values?

1_201
1_201
1_201
2_202
2_202
3_203





The second question is that when I make a table and only include the Customer and OrderID fields, it only shows distinct records.  I'm surprised that I don't see all records.  Is there a way to view all records?  


Thanks!

6 Replies
Highlighted
Specialist III
Specialist III

Re: How to Concatenate two Columns with Expression Editor

for the first :

add :

Customer&'_'&OrderID as NewField

 

example :

Orders:
LOAD Customer&'_'&OrderID as NewField,* INLINE [
Customer, OrderID, Product, Cost
1, 201, Chain, 20
1, 201, Seat, 40
1, 201, Mudguard, 50
2, 202, Gloves, 15
2, 202, Basket, 60
3, 203, Helmet, 70
];

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Specialist III
Specialist III

Re: How to Concatenate two Columns with Expression Editor

for the second can you share sample data and the output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Tags (1)
Highlighted
Contributor III
Contributor III

Re: How to Concatenate two Columns with Expression Editor

The output is just distinct values:

OrderID      Customer
201                1
202                2
203                3

Highlighted
Contributor III
Contributor III

Re: How to Concatenate two Columns with Expression Editor

Is there a way to do this in the expression editor?

For example, the problem I'm working through concatenates all of the Products with this expression:

=AGGR(Concat (DISTINCT Product, ', '),OrderID)


I had assumed that a simple expression like this would do what I wanted, but it doesn't work:

=Concat(Customer, '_', OrderID)

Highlighted
Specialist III
Specialist III

Re: How to Concatenate two Columns with Expression Editor

if you haven't use a load distinct  it's not a single line.
if you look at the internal table you can see :

 

Taoufiq_ZARRA_0-1593552525466.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "
Highlighted
Specialist III
Specialist III

Re: How to Concatenate two Columns with Expression Editor

something like this?

Orders:
LOAD * INLINE [
Customer, OrderID, Product, Cost
1, 201, Chain, 20
1, 201, Seat, 40
1, 201, Mudguard, 50
2, 202, Gloves, 15
2, 202, Basket, 60
3, 203, Helmet, 70
];

left join 
load OrderID,concat(distinct Product,',') as ConcatProduct resident Orders group by OrderID

 

output :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "