topic Re: Merging Multiple Rows into 1 in QlikView

Merging Multiple Rows into 1

Wed, 29 Apr 2015 09:49:46 GMT

Hi all,

I have OTC trade positions with two legs, one row per each leg, each row basically has the same data except for the nominal value. By writing a script in Qlikview, I want to merge these rows into 1, where I make a column for the far leg nominal (nominal 2). To clarify:

I load a table like this from a csv file:

Trade ID Trade Date Maturity Date Nominal

2204 11-02-2014 15-08-2015 50.000

2204 11-02-2014 15-08-2015 70.000

2205 16-03-2015 7-12-2016 20.000

2205 16-03-2015 7-12-2016 40.000

Using script, I want to merge the rows so that there will be one row per Trade ID, like this:

Trade ID Trade Date Maturity Date Nominal Nominal2

2204 11-02-2014 15-08-2015 50.000 70.000

2205 16-03-2015 7-12-2016 20.000 40.000

Any help would be appreciated. Thanks!

Re: Merging Multiple Rows into 1

anbu1984 — Wed, 29 Apr 2015 09:57:04 GMT

Load [Trade ID],[Trade Date],[Maturity Date],Min(Nominal) As Nominal,Max(Nominal) As Nominal2 Group By [Trade ID],[Trade Date],[Maturity Date];
Load * Inline [
Trade ID,Trade Date,Maturity Date,Nominal
2204,     11-02-2014,15-08-2015,     50.000
2204,     11-02-2014,15-08-2015,     70.000
2205,     16-03-2015,7-12-2016,       20.000
2205,     16-03-2015,7-12-2016,       40.000 ];

Re: Merging Multiple Rows into 1

Wed, 29 Apr 2015 10:47:47 GMT

If there is any chance of having more than 2 rows for each ID and/or the potential for the nominal to be higher then lower, then I would look to solving this with a generic load instead of the min/max group by

Have a look at this blog post for more on it

The Generic Load

hope that helps

Joe

Re: Merging Multiple Rows into 1

maxgro — Wed, 29 Apr 2015 11:52:21 GMT

source:

load *,

if(Peek([Trade ID])=[Trade ID],2,1) as Leg

inline [

Trade ID, Trade Date, Maturity Date , Nominal

2204 , 11-02-2014, 15-08-2015 , 50.000

2204 , 11-02-2014 , 15-08-2015 , 70.000

2205 , 16-03-2015 , 7-12-2016 , 20.000

2205 , 16-03-2015 , 7-12-2016 , 40.000

];

NoConcatenate load *

Resident source

where Leg=1;

Left join (t)

load [Trade ID], Nominal as NominalLeg2, Leg as Leg2

Resident source

where Leg=2;

DROP Table source;

Re: Merging Multiple Rows into 1

Wed, 29 Apr 2015 12:33:04 GMT

Hi Massimo,

Thanks for your help, this seems to be the solution! However, I do not totally understand the peek formula, could you briefly explain how this function works in this script? Thanks!

Re: Merging Multiple Rows into 1

maxgro — Wed, 29 Apr 2015 13:01:04 GMT

copied from QlikView help

be careful at the order when you read / load the record because the previous value (when you use peek(something)) depends on order

in my example the order is already ok

in a real situation usualy you have to use an order by and a resident load

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Examples:

peek( 'Sales' )
returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

peek( 'Sales', 2 )
returns the value of Sales from the third record read from the current internal table.

peek( 'Sales', -2 )
returns the value of Sales from the second last record read into the current internal table.

peek( 'Sales', 0, 'Tab1' )
returns the value of Sales from the first record read into the input table labeled Tab1.

Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.

Re: Merging Multiple Rows into 1

vamshi_1241 — Wed, 12 Sep 2018 12:32:52 GMT

How can we see 3rd column if we will have 3 rows like below

inline [

Trade ID, Trade Date, Maturity Date , Nominal

2204 , 11-02-2014, 15-08-2015 , 50.000

2204 , 11-02-2014 , 15-08-2015 , 70.000

2204 , 11-02-2014, 15-08-2015 , 85.000