topic Re: Logic Help,Above or Below Function in QlikView

Logic Help,Above or Below Function

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

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

Re: Logic Help,Above or Below Function

tresB — Wed, 16 Nov 2016 06:49:28 GMT

What is the logic of rearranging it?

Re: Logic Help,Above or Below Function

abhaysingh — Wed, 16 Nov 2016 06:58:29 GMT

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

Re: Logic Help,Above or Below Function

ramasaisaksoft — Wed, 16 Nov 2016 07:32:13 GMT

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)

Re: Logic Help,Above or Below Function

Anil_Babu_Samineni — Wed, 16 Nov 2016 07:32:30 GMT

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

Re: Logic Help,Above or Below Function

MindaugasBacius — Wed, 16 Nov 2016 07:59:33 GMT

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:

Re: Logic Help,Above or Below Function

Anil_Babu_Samineni — Wed, 16 Nov 2016 09:19:44 GMT

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: