topic Re: creating new field, group by? in QlikView

creating new field, group by?

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

Hi, please see table below. I would like to create a field that grabs only one score per ID, but that score has to be of the oldest date for that ID. please help!

So this new field should return 4 for bill, 8 for sam, and 9 for jon.

ID	score	date
bill	4	1/1/2016
bill	7	6/1/2016
sam	8	2/1/2016
sam	2	7/1/2016
jon	9	3/1/2016
jon	3	8/1/2016

Re: creating new field, group by?

vishsaggi — Tue, 06 Nov 2018 16:58:35 GMT

may be try this?

Score:

LOAD ID,

score,

date

FROM

[https://community.qlik.com/thread/319081]

(html, utf8, embedded labels, table is @1);

Right Join(Score)

LOAD ID,

Min(date) AS date

Resident Score

Group By ID;

Re: creating new field, group by?

drohm002 — Tue, 06 Nov 2018 18:11:51 GMT

does this create a new field or new table or? how can I get the field I want

Re: creating new field, group by?

vishsaggi — Tue, 06 Nov 2018 19:17:13 GMT

This will cut the data and give you only those values which has min date.

YOu can try below:

Score:

LOAD ID,

score,

date

FROM

[https://community.qlik.com/thread/319081]

(html, utf8, embedded labels, table is @1);

LEFT Join(Score)

LOAD ID,

Date(Min(date)) AS NewDate

Resident Score

Group By ID;

NoConcatenate

Final:

Load *,

IF(date = NewDate, score) AS NewScore

Resident Score;

Drop Table Score;

Re: creating new field, group by?

MarcoWedel — Tue, 06 Nov 2018 20:52:26 GMT

Hi,

one solution might be:

table1:
LOAD ID,
    FirstSortedValue(score, date) as FirstScore
FROM [https://community.qlik.com/thread/319081] (html, codepage is 1252, embedded labels, table is @1)
Group By ID;

hope this helps

regards

Marco

Re: creating new field, group by?

drohm002 — Wed, 07 Nov 2018 13:21:38 GMT

hi Marco, do I use the line below? like it is in yours...

FROM [https://community.qlik.com/thread/319081] (html, codepage is 1252, embedded labels, table is @1)

Re: creating new field, group by?

afroz_shaik — Wed, 07 Nov 2018 14:01:51 GMT

Hi Danieal,

you have to use "From YourTable".

Re: creating new field, group by?

drohm002 — Wed, 07 Nov 2018 14:30:33 GMT

what do you mean?? please show example

Re: creating new field, group by?

afroz_shaik — Wed, 07 Nov 2018 15:11:55 GMT

In Script your table must be named something right either it may be Excel, Sql Server eetc.. source. Your load statement looks like this

TableName:

Load ID,

FirstSortedValue(Score,date) As FirstScore

from Source

what is your source?

Re: creating new field, group by?

drohm002 — Wed, 07 Nov 2018 16:11:39 GMT

my source is a table called PELODDaily.....

Re: creating new field, group by?

MarcoWedel — Wed, 07 Nov 2018 22:16:42 GMT

Do you mean an already loaded (resident) table or a table in a connected DB?

Re: creating new field, group by?

qlikviewwizard — Thu, 08 Nov 2018 01:52:58 GMT

Hi Daniel Rohm,

Please check this.

Data:

LOAD ID,score,Date#(date,'MM/DD/YYYY') as Date INLINE [

ID,score,date

bill,4,1/1/2016

bill,7,6/1/2016

sam,8,2/1/2016

sam,2,7/1/2016

jon,9,3/1/2016

jon,3,8/1/2016

];

inner join(Data)

Data_Finala:

LOAD distinct ID,min(Date) as Date resident Data group by ID;