functional form

Community Support
1

Hi All,could anybody help me out to get a solution for the below issue:Req: I need to get the ids from the below table where values are inincreasing order (by id). (2009.05.01 value should be > next datesvalue ((2009.06.01 > 2009.05.01) and (2009.07.01 > 2009.06.01) and(2009.08.01 > 2009.07.01))Example:Table: tid period val--------------------------------1001 2009.05.01 50501001 2009.06.01 60001001 2009.07.01 70001001 2009.08.01 80001002 2009.05.01 55501002 2009.06.01 50001002 2009.07.01 55001002 2009.08.01 60501003 2009.05.01 98761003 2009.06.01 88761003 2009.07.01 78761003 2009.08.01 6876from the above rows the valid record is:id period val--------------------1001 2009.08.01 8000Thanks in advance,Jyoti

2

Hi Jyoti,

Hope this helps:

select id from (id xgroup period xasc t) where 0
Cheers,

2010/9/24 Jyoti <jyoti.pr>:
> Hi All,
>
> could anybody help me out to get a solution for the below issue:
>
>
> Req: I need to get the ids from the below table where values are in
> increasing order (by id). (2009.05.01 value should be > next dates
> value ((2009.06.01 > 2009.05.01) and (2009.07.01 > 2009.06.01) and
> (2009.08.01 > 2009.07.01))
>
> Example:
> Table: t
> id =A0 period =A0 =A0 =A0 =A0 val
> --------------------------------
> 1001 2009.05.01 5050
> 1001 2009.06.01 6000
> 1001 2009.07.01 7000
> 1001 2009.08.01 8000
> 1002 2009.05.01 5550
> 1002 2009.06.01 5000
> 1002 2009.07.01 5500
> 1002 2009.08.01 6050
> 1003 2009.05.01 9876
> 1003 2009.06.01 8876
> 1003 2009.07.01 7876
> 1003 2009.08.01 6876
>
> from the above rows the valid record is:
>
> id =A0 period =A0 =A0 val
> --------------------
> 1001 2009.08.01 8000
>
> Thanks in advance,
> Jyoti
>
> –
>

Submitted via Google Groups</jyoti.pr>

3

On Sep 24, 7:30?am, Jyoti <jyoti…> wrote:> Req: I need to get the ids from the below table where values are in> increasing order (by id). (2009.05.01 value should be > next dates> value ((2009.06.01 > 2009.05.01) and (2009.07.01 > 2009.06.01) and> (2009.08.01 > 2009.07.01))>> Example:> Table: t> id ? period ? ? ? ? val> --------------------------------> 1001 2009.05.01 5050> 1001 2009.06.01 6000> 1001 2009.07.01 7000> 1001 2009.08.01 8000> 1002 2009.05.01 5550> 1002 2009.06.01 5000> 1002 2009.07.01 5500> 1002 2009.08.01 6050> 1003 2009.05.01 9876> 1003 2009.06.01 8876> 1003 2009.07.01 7876> 1003 2009.08.01 6876>> from the above rows the valid record is:>> id ? period ? ? val> --------------------> 1001 2009.08.01 8000q)0!last each’select from(`id xgroup t)where val~'asc each valid period val--------------------1001 2009.08.01 8000–Aaron Daviesaaron.davies@gmail.com</jyoti…>

4

Thanks guys and sorry for late reply,

Actually none of solutions worked 100% for my requirement.

Lets assume my table is having the below rows:
q)meta t

c??? t f a
id??? i
period d
val?? f

q)select id,period,val from t
id??? period??? val

1001 2010.06.30 1000
1001 2010.07.31 1100
1001 2010.08.31 1400
1001 2010.09.30 1500

1002 2010.06.30 2341
1002 2010.07.31 5341
1002 2010.08.31 3341
1002 2010.09.30 5679

1003 2010.06.30 8765
1003 2010.07.31 7765
1003 2010.08.31 6213
1003 2010.09.30 513

1004 2010.06.30 1763
1004 2010.07.31 8763
1004 2010.08.31 8763
1004 2010.09.30 9109

1005 2010.06.30
1005 2010.07.31 1119
1005 2010.08.31 1509
1005 2010.09.30 2509

1006 2010.06.30
1006 2010.07.31
1006 2010.08.31 1234
1006 2010.09.30 1123

Valid results are:
id??? period??? val

1001 2010.09.30 1500
1005 2010.09.30 2509

Solutions:
1.
select id from (id xgroup period xasc t) where 0<min 0-':val
->Empty response

q)0!last each’select from(`id xgroup t)where val~'asc each val
id?? period??? val

1001 2010.09.30 1500
1004 2010.09.30 9109 ===> Invalid record
1005 2010.09.30 2509

Not working for all requiremnets (where all next values are same as pervious)

  1. Need to Check:
    select rowid,period,val from (select av:all(deltas val)>0, max period,max val by rowid from `period xasc t) where av=1
    Response:
    id?? period??? val

1001 2010.09.30 1500

need id=1005 too

please let me know if the requirement is still not clear

Thanks,
Jyoti

5

q)select by id from t where(all(>':)0^;val)fby id?

id? period??? val
1001 2010.09.30 1500
1005 2010.09.30 2509
? Attila
6

0!last each’select from (id xgroupid`period xasc t) where all each
0id period val

1001 2010.09.30 1500
1005 2010.09.30 2509

7

Thanks Attila… working fine

8

Attilla and Aaron were gracious to respond, but it would have been preferable? if you had? at least made an attempt to solve your problem? - like? reading the manual - , instead of looking for someone on the list to do the thinking for you.

BC

9

Hi Bishop,

I agree. Actually I tried few attempts but failed to meet the requirement, I have posted this as I was running out of time. In future, I’d try to fix it at my own.

Thanks,