topic Re: create variable or expression in QlikView

create variable or expression

drohm002 — Wed, 25 Nov 2020 16:16:04 GMT

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.

ID	Score	Date	Company
bill	4	1/1/2016	Blue
bill	7	6/1/2016	Blue
sam	8	2/1/2016	Blue
sam	2	7/1/2016	Blue
jon	9	3/1/2016	red
jon	3	8/1/2016	red
adam	11	5/1/2016	red
adam	6	7/1/2016	red

Re: create variable or expression

martinpohl — Thu, 08 Nov 2018 15:13:52 GMT

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

Re: create variable or expression

drohm002 — Thu, 08 Nov 2018 15:33:37 GMT

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...

Re: create variable or expression

drohm002 — Thu, 08 Nov 2018 15:43:20 GMT

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

Re: create variable or expression

andrey_krylov — Thu, 08 Nov 2018 16:53:04 GMT

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

Re: create variable or expression

drohm002 — Thu, 08 Nov 2018 17:01:08 GMT

is TOTAL a function?

Re: create variable or expression

sergio0592 — Thu, 08 Nov 2018 17:33:20 GMT

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.