Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
can you post sample data? and expected output
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.
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;
Hi Avinash,
The earned column is not there in the source table. It's an addition. in the target table using the formula.
Please share the sample app with the data set ...will check and let you know
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,
...
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;
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