Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

drohm002
New Contributor

creating new field, group by?

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.

  

IDscoredate
bill41/1/2016
bill76/1/2016
sam82/1/2016
sam27/1/2016
jon93/1/2016
jon38/1/2016
11 Replies
vishsaggi
Esteemed Contributor III

Re: creating new field, group by?

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;

Highlighted
drohm002
New Contributor

Re: creating new field, group by?

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

vishsaggi
Esteemed Contributor III

Re: creating new field, group by?

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?

Hi,

one solution might be:

QlikCommunity_Thread_319081_Pic1.JPG

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

drohm002
New Contributor

Re: creating new field, group by?

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)

afroz_shaik
New Contributor III

Re: creating new field, group by?

Hi Danieal,

you have to use "From YourTable".

drohm002
New Contributor

Re: creating new field, group by?

what do you mean?? please show example

afroz_shaik
New Contributor III

Re: creating new field, group by?

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?

drohm002
New Contributor

Re: creating new field, group by?

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