topic Concat, different rows into one row in QlikView

Concat, different rows into one row

jharke — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Concat, different rows info one rowConcat, different rows info one row

Gysbert_Wassenaar — Fri, 25 Mar 2016 16:20:39 GMT

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

Re: Concat, different rows info one rowConcat, different rows info one row

sunny_talwar — Sun, 27 Mar 2016 23:00:43 GMT

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];

Re: Concat, different rows info one rowConcat, different rows info one row

tyagishaila — Mon, 28 Mar 2016 06:30:03 GMT

Try it,

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

Cheers,

Re: Concat, different rows info one rowConcat, different rows info one row

jharke — Tue, 29 Mar 2016 06:45:06 GMT

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

Re: Concat, different rows info one rowConcat, different rows info one row

jharke — Fri, 01 Apr 2016 06:24:51 GMT

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?

Re: Concat, different rows info one rowConcat, different rows info one row

Kushal_Chawda — Fri, 01 Apr 2016 07:38:28 GMT

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], ', ')

Re: Concat, different rows info one rowConcat, different rows info one row

jharke — Fri, 01 Apr 2016 07:59:54 GMT

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

Re: Concat, different rows info one rowConcat, different rows info one row

Gysbert_Wassenaar — Fri, 01 Apr 2016 08:08:14 GMT

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

Re: Concat, different rows info one rowConcat, different rows info one row

jharke — Fri, 01 Apr 2016 08:13:32 GMT

almost 🙂