Peek function not working properly.

[mickartik]

Hi All,

Please find below the script.

I should be getting a "previous+1" value in increment order for field earned & earned2.

I'm using Peek function and getting null values.

Can you please verify the code? What I am doing wrong in this?

temp:
LOAD _MASTER_KEY,
Peek(_MASTER_KEY),
earned_temp as tt,
Peek(earned_temp),

Peek('tt') +1 as earned,
Peek(tt) as earned2,
Resident TRptTransSummary
ORDER BY _MASTER_KEY;

earned_temp holds '0' from the previous query.

[vinieme12]

temp:
LOAD _MASTER_KEY,
Peek(_MASTER_KEY),
earned_temp as tt,
Peek(earned_temp),

rangesum(Peek(earned),1) as earned,
Peek(tt) as earned2,
Resident TRptTransSummary
ORDER BY _MASTER_KEY;

[vinieme12]

can you post sample data? and expected output

[mickartik]

Hi vinieme12,

Below is the sample data

_MASTER_KEY
1
2
3
4
5
6
7
8
9
10

Below is the sample required output

_MASTER_KEY	earned	previous_value
1	0.0625	0.5
2	0.044270833	0.0625
3	0.043511285	0.044270833
4	0.043479637	0.043511285
5	0.043478318	0.043479637
6	0.043478263	0.043478318
7	0.043478261	0.043478263
8	0.043478261	0.043478261
9	0.043478261	0.043478261
10	0.043478261	0.043478261

Formula: earned = [(Previous month value + 1)/24]

When there's no previous value then it should be taken as 0.5

As can be seen in sample output the previous_value should be with in 0.5 to 24.5. Once it reaches 24.5 then again it starts from 0.5.

[avinashelite]

check the attachment for solution

Temp:

LOAD * Inline

[

_MASTER_KEY,earned

1,0.0625

2,0.044270833

3,0.043511285

4,0.043479637

5,0.043478318

6,0.043478263

7,0.043478261

8,0.043478261

9,0.043478261

10,0.043478261

];

NoConcatenate

LOAD _MASTER_KEY,

earned,

if(RowNo()=1,'0.5',Peek('earned')) as Pre

Resident

Temp;

Drop Table Temp;

[mickartik]

Hi Avinash,

The earned column is not there in the source table. It's an addition. in the target table using the formula.

[avinashelite]

Please share the sample app with the data set ...will check and let you know

[jonathandienst]

Like this?

...

If(Len(Peek(earned)) = 0, 0.5, Peek(earned)) as previous_value,

If(Len(Peek(earned)) = 0, 0.5, (Peek(earned) + 1) / 24) as earned,

Alt(Peek(earned), 0.5) as previous_value,

(1 + Alt(Peek(earned), 0.5)) / 24 as earned,

...

[parimikittu]

Hi,

Please check the below code and Let me know if it works fine.

_MASTER_KEY:

load * Inline [

monther

1

2

3

4

5

6

7

8

9

10

];

MASTER_KEY:

load monther,

if(isnull(peek(previous_value)),0.5,(peek(previous_value)+1)/24) as previous_value

Resident _MASTER_KEY;

[rwunderlich]

According to your screenshot, you appear to have QUALIFY in effect. If so, you will need to refer to the fields in peek by their qualified names: 

Peek(temp.tt) as earned2,

-Rob

http://masterssummit.com

http://qlikviewcookbook.com