Logic Help,Above or Below Function

[abhaysingh]

Hi All,

This is my Sample Data

load * inline

[

Month,Rate,Qtr

Mar,10,Q1

Apr,12,Q1

May,12,Q1

Jun,12,Q2

Jul,13,Q2

Aug,13,Q2

Sep,13,Q3

Oct,14,Q3

Nov,14,Q3

Dec,14,Q4,

Jan,10,Q4

Feb,10,Q4];

output Required

load * inline

[

Month,Rate,Qtr

Mar,12,Q1

Apr,12,Q1

May,12,Q1

Jun,13,Q2

Jul,13,Q2

Aug,13,Q2

Sep,14,Q3

Oct,14,Q3

Nov,14,Q3

Dec,10,Q4,

Jan,10,Q4

Feb,10,Q4];

Any suggestion

[tresesco]

What is the logic of rearranging it?

[abhaysingh]

Not rearranging, first one is input second one is desired output,

u can see in input table march rate is 10, but in desired output March Rate 12 which is the Rate of April.

There Are decided Months where i want next month rate value.

thanks

[ramasaisaksoft]

t1:

load * inline

[

Month,Rate,Qtr

Mar,10,Q1

Apr,12,Q1

May,12,Q1

Jun,12,Q2

Jul,13,Q2

Aug,13,Q2

Sep,13,Q3

Oct,14,Q3

Nov,14,Q3

Dec,14,Q4,

Jan,10,Q4

Feb,10,Q4

];

t2:

load Month,max(Rate) as R,Qtr

Resident t1

Group by Qtr,Month;

//drop table t1;

will give u solution and in Table box sort by order like(Month,Qtr,R,Rate)

[Anil_Babu_Samineni]

I hope you required, Same Rate for Same Quarter which  is the next month Value? Does it you are looking?

[MindaugasBacius]

I am not sure if this would work all the time under your circumstances:

Table:

load *

inline

[

ID,Month,Rate,Qtr

1,Mar,10,Q1

2,Apr,12,Q1

3,May,12,Q1

4,Jun,12,Q2

5,Jul,13,Q2

6,Aug,13,Q2

7,Sep,13,Q3

8,Oct,14,Q3

9,Nov,14,Q3

10,Dec,14,Q4

11,Jan,10,Q4

12,Feb,10,Q4]

;

tmp:

LOAD *

  ,If(ID = 12, Rate, Peek(Rate)) as [New Rate]

Resident Table

Order by ID desc

;

DROP Table Table;

Result:

[Anil_Babu_Samineni]

Better Approach could be this, Some how we don't have ID with us. So you may create Preceding Load

I've added simple function with the following ID like

Rowno() as ID

Mindaugas Bacius wrote:

I am not sure if this would work all the time under your circumstances:

Table:

load *, Rowno() as ID

inline

[

Month,Rate,Qtr

Mar,10,Q1

Apr,12,Q1

May,12,Q1

Jun,12,Q2

Jul,13,Q2

Aug,13,Q2

Sep,13,Q3

Oct,14,Q3

Nov,14,Q3

Dec,14,Q4

Jan,10,Q4

Feb,10,Q4]

;

tmp:

LOAD *

  ,If(ID = 12, Rate, Peek(Rate)) as [New Rate]

Resident Table

Order by ID desc

;

DROP Table Table;

Result: