Previous and Peek - Script

[#1]

Hi Guys,

Both my previous and peek functions on the same field return a value on the same line. I assumed that previous would check my if statement for the previous line and peek the next line. What am I doing wrong or not understanding. Also, if this doesnt make sense let me know (of course) and I will add a table as an example

Cheers,

Byron

[#2]

Thanks Miguel and Kaushik. I will try both solutions now. In the meantime I have created a test QVD document that hopefully explains the problem a little better. As mentioned will try the above solutions quickly and let you know

[#3]

BTW I must state. You may look at the above document and say while the sum of those values is 0 so it is removed. Yes true, but I have used group by's to pull a specific table in a specific order. However, the ordering always ensures that these two values are next to each so if there is a way to sum them to be removed, then great

[#4]

Hi Again all, apologies for the multiple posts. The below code, taken from miguel now adds a 1 to one line. As shown in the attached QVD, i now need to add a 1 to the previous line. I can always later edit this so it is a null, for now, using a 1 is a lot easier. I thought the below would work, but it doesnt. The idealogy behind it is what I need to get to work

DuplicateFinal:
NOCONCATENATE
LOAD *,
rowno() as d_rownoID,
If(Previous(d_positivehours) = d_positivehours AND d_signhours = 'N', 1) AS Remove
Resident DuplicatePrefinal
Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_timesheetno, d_invoice;
Drop Table DuplicatePrefinal;

FinalDuplicate:
LOAD
if(peek(Remove)=1,1) as Remove
Resident DuplicateFinal;

[#5 · Accepted solution]

Hi All,

I managed to put together some code that gave me the correct result. In my straight table, I now have a if clause to supress any null values where Ignore = X. Here is the code if anyone is interested

DuplicatePrefinal:
LOAD
ClientAccount,
TempName as d_tempname,
CategoryDesc as d_categorydesc,
WkEndDate as d_wkenddate,
TimesheetNo as d_timesheetno,
Invoice as d_invoice,
Duplicate as d_duplicate,
Hours as d_hours,
if(Hours<0,'N','P') as d_signhours,
Fabs(Hours) as d_positivehours,
Fabs(ChargeValue) as d_positivechargevalue,
ChargeValue as d_chargevalue,
PayValue as d_payvalue
Resident InvoicingPreFinal
Where Duplicate = 'Check'
Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;


DuplicateFinal:
NOCONCATENATE
LOAD *,
rowno() as d_rownoID
Resident DuplicatePrefinal
Order by d_tempname, d_wkenddate, d_categorydesc, d_positivehours, d_signhours desc, d_timesheetno, d_invoice, d_positivechargevalue desc;
Drop Table DuplicatePrefinal;
ReversalsRemovePrefinal:


Qualify *;
UNQUALIFY d_rownoID;
LOAD *
Resident DuplicateFinal
Where d_signhours='N' and previous(d_hours)=d_positivehours and previous(d_chargevalue)=d_positivechargevalue and not isnull(d_tempname);
UNQUALIFY *;

Concatenate(ReversalsRemovePrefinal)
LOAD (d_rownoID - 1) as d_rownoID,
ReversalsRemovePrefinal.d_tempname
Resident ReversalsRemovePrefinal;



ReversalsRemoveFinal:
LOAD
d_rownoID as ReversalsRemoveFinal.d_rownoID,
d_rownoID,
'X' as d_ignore
Resident ReversalsRemovePrefinal
Where not isnull(ReversalsRemovePrefinal.d_tempname);
Drop Table ReversalsRemovePrefinal;