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]

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]

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]

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.

[Anonymous]

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;

[#8]

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]

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