Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Select first value only

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.

1 Solution

Accepted Solutions
eduardo_sommer
Valued Contributor

Re: Select first value only

Use this:

Table2:

LOAD

account_nbr

first(production_date) as production_date

Resident Table1

where fieldName = 1

Group by account_nbr;

Eduardo

6 Replies
Employee
Employee

Re: Select first value only

Have you tried where exists()?

mwoolf
Honored Contributor II

Re: Select first value only

Try putting First 1 above the load.

Not applicable

Re: Select first value only

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

Not applicable

Re: Select first value only

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

MVP
MVP

Re: Select first value only

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

eduardo_sommer
Valued Contributor

Re: Select first value only

Use this:

Table2:

LOAD

account_nbr

first(production_date) as production_date

Resident Table1

where fieldName = 1

Group by account_nbr;

Eduardo

Community Browser