topic Re: Return only 1 record in a table in QlikView

Return only 1 record in a table

Tue, 26 Apr 2016 12:30:53 GMT

I have a table that contains employee number and employee name, in some cases the table has 2 records

Employee no EmployeeName

20000005 JOE BLOGS

20000005 joe blogs

I need to return just 1 record

Re: Return only 1 record in a table

sunny_talwar — Tue, 26 Apr 2016 12:32:48 GMT

May be try using FirstValue or LastValue function in the script:

LOAD EmpNO,

FirstValue(EmpName) as EmpName

Resident YourTable

Group By EmpNo;

Re: Return only 1 record in a table

PrashantSangle — Tue, 26 Apr 2016 12:34:26 GMT

Hi,

Then what do you want to do with 2 emplyoee Name

Try concat()

try like

Load EmployeeNo,

Concat(EmployeeName) as CombineEmployeeName

From TableName;

Kind Regards

Re: Return only 1 record in a table

Kushal_Chawda — Tue, 26 Apr 2016 12:44:23 GMT

Try like this if you have more fields in table add remaining field in group by

Data:

load Empno

Firstvalue(Empname) as Empname

Group by Empno;

load Empno,

Capitalize(Trim(Empname)) as Empname

From Table;

Re: Return only 1 record in a table

ThornOfCrowns — Tue, 26 Apr 2016 12:46:32 GMT

Which one of those two records is 'correct'?

Re: Return only 1 record in a table

avinashelite — Tue, 26 Apr 2016 12:46:51 GMT

As per you data sample , due to the case miss match its showing as two different records try to convert then to common format using Capitalize() or Upper () function will do that

LOAD [Employee no],

Capitalize([EmployeeName]) as EmployeeName,

//Upper(EmployeeName) as EmployeeName

Re: Return only 1 record in a table

sunny_talwar — Tue, 26 Apr 2016 12:48:37 GMT

Good to question to ask