topic Re: Select first value only in QlikView

Select first value only

Thu, 29 May 2014 17:06:21 GMT

In my script I have defined a table that loads in fields called account_nbr and production_date. I am limiting these field values that are loaded in by only loading in those where another field equals a certain value. This is what it looks like:

Table2:

LOAD

account_nbr

production_date

Resident Table1

where fieldName = 1;

My goal is to only load the first row for each account_nbr where fieldName = 1. Is this possible? Right now I am getting multiple rows loaded for each account_nbr since fieldName = 1 for more than one production_date. To put it more simply, I am only interested in loading the account_nbr and production_date values where the fieldName = 1 for the first time, and nothing after that. Thanks for the help.

Re: Select first value only

Greg_Williams — Thu, 29 May 2014 17:14:52 GMT

Have you tried where exists()?

Re: Select first value only

m_woolf — Thu, 29 May 2014 17:18:17 GMT

Try putting First 1 above the load.

Re: Select first value only

Thu, 29 May 2014 17:18:45 GMT

How would this only select the top row for each account_nbr?

Re: Select first value only

Thu, 29 May 2014 17:20:41 GMT

This only loads one row. I'm interested in loading the first row for each of several account numbers.

Re: Select first value only

maxgro — Thu, 29 May 2014 17:22:20 GMT

try with (in bold the most important piece of code)

table:

load rowno() as id, * inline [

account_nbr, production_date, fieldName

1,11,0

1,2,0

1,31,1

1,4,1

1,5,0

2,61,0

2,7,1

2,8,1

];

final:

NoConcatenate

load *

Resident table

where fieldName = 1

and peek(account_nbr) <> account_nbr

order by account_nbr, id;

DROP Table table;

EDIT: if you want the first by date, order by date instead of id

Re: Select first value only

eduardo_sommer — Thu, 29 May 2014 19:15:24 GMT

Use this:

Table2:

LOAD

account_nbr

first(production_date) as production_date

Resident Table1

where fieldName = 1

Group by account_nbr;

Eduardo