Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 :
for the second can you share sample data and the output ?
The output is just distinct values:
OrderID Customer
201 1
202 2
203 3
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)
if you haven't use a load distinct it's not a single line.
if you look at the internal table you can see :
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 :