stuck with group by and where

[curiousfellow]

I have an existing order table (clientno,orderdate,amount)

Now I want to add a field to that table, containing the first order date per client

I think I have to add the new field by using Mapping ?

this is my script :

order_temp:

Mapping load clientno, orderdate

resident order

where orderdate = min(orderdate)

group by clientno;

However execution stops at this point and I can not figure out why.

[stabben23]

Hi, cjange to this:

order_temp:

Mapping load clientno, min(orderdate) as orderdate

resident order

group by clientno;

[trdandamudi]

May be as below:

order_temp:

Mapping load clientno , min(orderdate)

resident order

group by clientno;

[mohammadkhatimi · Accepted solution]

Hi,

Try this

firstsortedvalue(clientno ,-orderdate)

May be this will helps u..!!

Regards,

Mohammad.

[curiousfellow]

Sorry,

I wanted to simplify my question, but my intention is to add another field than min(orderdate)

so my script is

this is my script :

order_temp:

Mapping load clientno, name_contact

resident order

where orderdate = min(orderdate)

group by clientno;

The field that I want to add can change. so I want to have the value belonging to the first order date

[mohammadkhatimi]

Use firstsortedvalue function...

[trdandamudi]

See if the below works for you....

order:

Load * Inline [

clientno, orderdate,name_contact

10,10/10/2016,XYZ

10,10/22/2016,ABC

20,10/10/2016,QQQ

];

order_temp:

Mapping load clientno, firstsortedvalue(name_contact ,orderdate) as name_contact

resident order

group by clientno;

[stabben23]

Like this or as above With Firstsortedvalue.

order_temp:

Mapping load clientno, name_contact, min(orderdate) as orderdate

resident order

group by clientno, name_contact;

[Anonymous]

order_temp:

Mapping load clientno, min(orderdate) as orderdate

resident order

group by clientno;