Combine data from different rows in 1 row with measure

[Eckhardt_david]

Hi all,

I need to build a report where data from below table are combined into 1 row.

I have no access to the datamodel so it has to be done via a measure.

The below table is showing the data as is, filtered on the 3 transactions for LODNUM 354200224066522111.

I have to combine this data with below requirements to get 1 row per LODNUM and DateValue.

Requirements:

get SHIP_ID where ACTCOD = INVCRE
get FRSTOL where OPRCOD = UTR
get TOSTOL where ACTCOD = FL_XFR and OPRCOD = UID

Desired result:

DateValue	LODNUM	SHIP_ID	FRSTOL	TOSTOL	M_TRNQTY
14/09/2021	354200224066522111	0185170258	2A-RCVL-20	2A-18-190-40	450

Used tables and fields:

Table	Field
08-D_INTERNAL_PER_INPUT	FRSTOL
08-D_INTERNAL_PER_INPUT	LODNUM
08-D_INTERNAL_PER_INPUT	OPRCOD
08-D_INTERNAL_PER_INPUT	SHIP_ID
08-D_INTERNAL_PER_INPUT	TOSTOL
08-C_INTERNAL_PER_INPUT	DateValue
08-F_INTERNAL_PER_INPUT	M_TRNQTY

Your help is much appreciated!

[lbendlin · Accepted solution]

You will need to create three measures, one for each of the values that you want to achieve.  Then you remove all columns from your visual that prevent grouping ( everything except datevalue,Lodnum,m_trnqty)  and add the measures instead.