Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Previous and Peek - Script

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

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

13 Replies
Not applicable
Author

Hi Byron,

Previous looks in the previous line of the data being loaded (not discarded by a where clause), not from the table that is being created in QlikView. You have to use the column names of the source table, not the column names of the table in QV.

Peek looks into the table that is being created in the QV data cloud. This is done at runtime while the table is being populated by the script. It is thus not possible to look ahead to the next row in a load statement.

Regards,

Bert

lironbaram
Partner - Master III
Partner - Master III

hei

both functions work on the pervious records

previous refers to the previous record only

while in pick you can spcifey the numer of row you want to go to

post an example please so i can understand your problem

its_anandrjs

Hi,

Could you share any sample file for that it goes easy.

Regards,

Anand

Miguel_Angel_Baeyens

Hi Byron,

Check this post or this other post that faces an similar issue. Both are examples on the use of Previous() and Peek() and you will find many more digging a bit in the Community.

As Liron says above, both Previous and Peek refer to already loaded values. The main difference is that Previous() refers only to the immediately previously loaded record, while Peek() may return any existing value from a field.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

SunilChauhan
Champion II
Champion II

please see the attched file

previous used to pull previous record

where as peek is used to pull both first and last record

using -1 and 0.

Sunil Chauhan
Anonymous
Not applicable
Author

By using 'asc' or 'desc' in the order by clause you can get the previous or the next value.

example

tmp:

load * inline

[id,name

1,'Jack'

2,'John'

3,'Mike'

4,'Nic'

];

example1:

load

id,

  name as name1,

previous(name) as prev

resident tmp

order by id asc;

example2:

load

id,

  name as name2,

previous(name) as next

resident tmp

order by id desc;

qlikview.png

Not applicable
Author

Thanks to all. Every response has been informative, all deserves helpful answers. I think it best to show you guys what I am trying to do and maybe someone can point me in the right direction. I may whiile be doing this all wrong, we will find out

DuplicatePrefinal:
LOAD *
Where d_duplicate = 'Check';
LOAD
ClientName,
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
Resident SPInvoicingPreFinal
Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;

DuplicateFinal:
LOAD *,
rowno() as d_rownoID,
1
as Dummy, //Dummy field to not force automatic concatenation
/* EXAMPLE OF CODE I WANT TO ADD TO DO SOMETHING */
if(peek(d_positivehours)<>d_positivehours /*current line*/ 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;
Drop Field Dummy;


The above script is pretty much the part that I am having problem with. Essentially, refer to the comment lines. The peek function I use I want to add a null value to the line items. I will then suppress this as a dimension in my straight table as I want to exclude from the table I have already manipulated. I guess posting the entire script is not the best way to go, but hoping one of you brilliant people understands what I am trying to do. PLEASE, if there is a better approach, PLEASE DO TELL

Many thanks in advance!

Miguel_Angel_Baeyens

Byron,

Check the following modified script

DuplicatePrefinal:
LOAD ClientName,
     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
Resident SPInvoicingPreFinal

WHERE Duplicate = 'Check' // set the WHERE here, save one preceding load
Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;

DuplicateFinal:
NOCONCATENATE LOAD *,
     RowNo() AS d_rownoID,
     // 1 as Dummy, // No longer need this
     // the following compares the previous loaded value of d_positivehours field against the current
     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;

Drop Field Dummy;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Try this way.

   

DuplicatePrefinal:
LOAD *
Where d_duplicate = 'Check';


LOAD
ClientName,
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
Resident SPInvoicingPreFinal
Order by TempName, WkEndDate, CategoryDesc, TimesheetNo, Invoice;

DuplicateFinal:
LOAD *,
rowno() as d_rownoID,
1 as Dummy,

if(peek('d_positivehours',rowno(),'DuplicatePrefinal')<>d_positivehours /*current line*/ 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;
Drop Field Dummy;

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!