Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
drohm002
New Contributor

create variable or expression

Hi, please see table below.  How can I create a variable or an expression that returns only one score per company, based on the earliest date?

Thank you.

   

IDScoreDateCompany
bill41/1/2016Blue
bill76/1/2016Blue
sam82/1/2016Blue
sam27/1/2016Blue
jon93/1/2016red
jon38/1/2016red
adam115/1/2016red
adam67/1/2016red
6 Replies
Partner
Partner

Re: create variable or expression

Do it in the script:

load YourData;

FirstScore:

load distinct Company

resident YourData;

left join load

Company,

min(Date) as FirstDate

resident YourData

Group by Company;

left join load

Company,

Date as FirstDate,

Score as FirstScore

resident YourData;

drop field FirstDate;

Regards

drohm002
New Contributor

Re: create variable or expression

is it possible to not do it in the script?  the company field is from a different table than all the other fields so I'm having trouble using Company in the script...

drohm002
New Contributor

Re: create variable or expression

I have a flag set up to find the min date.  can we use that in a variable or expression to help us?

andrey_krylov
Valued Contributor

Re: create variable or expression

Hi Daniel. Maybe FirstSortedValue(TOTAL <Company> Score, Date)

Highlighted
drohm002
New Contributor

Re: create variable or expression

is TOTAL a function?

sergio0592
Valued Contributor II

Re: create variable or expression

TOTAL is not a function. It's a key word which can be explained with :

The TOTAL qualifier is added to aggregation functions to disregard chart

dimensions and make the calculation over the entire record set as defined

by the current selection state.