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 ];
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
hope that helps
if(Peek([Trade ID])=[Trade ID],2,1) as Leg
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 *
Left join (t)
load [Trade ID], Nominal as NominalLeg2, Leg as Leg2
DROP Table source;
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.
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.