Concat, different rows into one row

[jharke]

Good afternoon

We transport different kind of goods. All those types of goods are shown in QV with one row per type, as in the prinstcreen below. E.g ordernumber 1 is a consignment with 2 pallets + 4 rolls + 6 bundles. Because of the 3 types in 1 consignment, I get 3 rows in QV.

I 'd like to have 1 row, but with all the kinds of goods shown, as at the bottem of the printscreen below.

I think I have to use concat.

Until now I have: =sum ({$} [Colli]) &  concat([kind of goods], ',') .

But what I get is:   12pallet,roll,bundle

I like to get: 2x pallet, 4x roll, 6x bundle (or 2pallet, 4roll, 6 bundle. So without "x" is also good)

Het bericht is bewerkt door: J Harke

[Gysbert_Wassenaar]

Try =concat( Colli & 'x ' & [kind of goods], ', ')

[sunny_talwar]

You can also apply Gysbert's expression in the script also:

Table:

LOAD * Inline [

order nr, Colli, kind of goods

1, 2, pallet

1, 4, roll

1, 6, bundle

2, 1, bale

2, 4, crate

2, 3, pallet

3, 2, box

4, 4, box

5, 6, roll

5, 1, bale

];

Join (Table)

LOAD [order nr],

  Concat(Colli & 'x ' & [kind of goods], ', ') as [Order Detail]

Resident Table

Group By [order nr];

[tyagishaila]

Try it,

aggr(concat(Colli&'x'&[Kinds of Goods],','), OrderNo)

Cheers,

[jharke]

Thank you Gysbert and Tyagishaila.

Both will do a bit.

I have got also orders like this:

Also, with each item a row (as it is given to us by our customer per EDI per row), and I want to have a Sum of this --> ordernr 4= 6x pallet, 2x box

[jharke]

Thank you Tyagishaila.

Your solution will do a bit, but...

I have got also orders like this:

Also, with each single item one row (as it is given to us by our customer by EDI per row, with references and information per single item etc), and I want to have a Sum of this --> ordernr 4= 6x pallet, 2x box. Is that possible?

[Kushal_Chawda]

Try,

LOAD Orderno, 

            [Kind of goods] as [Kind of goods 1] ,

            sum(Colli) as ColliNew

Resident YourTable

group by  

Orderno,  [Kind of goods];

concat( ColliNew & 'x ' & [Kind of goods 1], ', ')

[jharke]

Goodmorning Gysbert,

We are coming closer:

Now I got:

So, the sum is OK, but I don't want to see the 'empty' Euro pallet or Rol

[Gysbert_Wassenaar · Accepted solution]

concat(distinct aggr(nodistinct sum(Colli) & 'x ' , [order nr], [kind of goods]) &  [kind of goods],', ',[kind of goods])

[jharke]

almost 🙂