topic Re: select one value only in QlikView

select one value only

Wed, 25 Nov 2020 16:16:04 GMT

I want to ask how I can select only one value in qlikview?

I have for example one employer but registered in two company in the same month .

I already have some condition in set analysis

Count({$<_TransType={'CONTRACT'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=>} DISTINCT %COMPANY_NUMBER)

Re: select one value only

Anil_Babu_Samineni — Sun, 25 Dec 2016 14:03:19 GMT

Does this selection one value from Listbox then you may look this. or can you describe more

Re: select one value only

vinieme12 — Sun, 25 Dec 2016 15:45:10 GMT

please post some sample data/ app to explain clearly the problem and expected output.

QlikCommunity Tip: How to get answers to your post?

Re: select one value only

Tue, 27 Dec 2016 04:02:46 GMT

dear,

while loading the data in qvd, there are two row for same employer in same month and same year,

I want to select only one value ,according to the expression which I posted earlier with distinct it's not worked because I have two different companies.

Re: select one value only

vinieme12 — Wed, 28 Dec 2016 04:06:25 GMT

So is it like below

Employer,year,month,Company

A,2016,1,ABC

A,2016,1,XYZ

On what basis would you want to drop a row?

Re: select one value only

Wed, 28 Dec 2016 04:38:59 GMT

I have this key, it's concatenated of Employer no, Company no, branch no and last value is registration start date , I want to select the max number or registration start date for each Employer

and with certain condition like this :

Count({$<_TransType={'CONTRACT'},_YearMonth={'$(vCurrentMonth)'},Month=,Year=,[Month AR]=>} DISTINCT %COMPANY_NUMBER)

by this expression I get two ,but I want only one value (according the max value of registration start date)

Re: select one value only

engishfaque — Wed, 28 Dec 2016 04:58:47 GMT

Dear Maeda,

Distinct Keyword used to return only distinct values and it checks/compare all the columns (fields) data if you are loading more than one field. Means it check/compare whole row data with another data if there is any one change it will return you the value.

For clear understanding, example is given below

Example 1 with Distinct Keyword Output

----

Input
Emp Name	Company
Ishfaque	ABC
Ishfaque	XYZ

Output with Distinct Keyword
Emp Name	Company
Ishfaque	ABC
Ishfaque	XYZ

----

Example 2 with Distinct Keyword Output

----

Input
Emp Name	Company
Ishfaque	ABC
Ishfaque	ABC

Output with Distinct Keyword
Emp Name	Company
Ishfaque	ABC

Kind regards,

Ishfaque Ahmed

Re: select one value only

Wed, 28 Dec 2016 05:09:06 GMT

I understand distinct but in my expression I need to add some thing like max or aggr but how I write them in my example?!

Re: select one value only

vinieme12 — Wed, 28 Dec 2016 15:24:41 GMT

then try

Load Distinct Key,

MAX(Registration_Date) as RegistrationDate

FROM YOURSOURCE

GROUP BY Key,remainingfield1,remainingfield2.....and so on

Re: select one value only

Sun, 01 Jan 2017 04:39:38 GMT

I will apply this but in this way, I have to write group by

let me try and then I will post the result of this solution

regards;