topic Previous and Peek - Script in QlikView

Previous and Peek - Script

Tue, 23 Aug 2011 12:14:47 GMT

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

Previous and Peek - Script

Tue, 23 Aug 2011 12:22:55 GMT

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

Previous and Peek - Script

lironbaram — Tue, 23 Aug 2011 12:25:39 GMT

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

Previous and Peek - Script

its_anandrjs — Tue, 23 Aug 2011 12:32:46 GMT

Hi,

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

Regards,

Anand

Previous and Peek - Script

Miguel_Angel_Baeyens — Tue, 23 Aug 2011 12:39:21 GMT

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

Re: Previous and Peek - Script

SunilChauhan — Tue, 23 Aug 2011 12:46:37 GMT

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.

Re: Previous and Peek - Script

Anonymous — Tue, 23 Aug 2011 12:52:32 GMT

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;

Previous and Peek - Script

Wed, 24 Aug 2011 10:11:58 GMT

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!

Re: Previous and Peek - Script

Miguel_Angel_Baeyens — Wed, 24 Aug 2011 10:31:19 GMT

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

Previous and Peek - Script

kaushiknsolanki — Wed, 24 Aug 2011 10:38:12 GMT

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

Re: Previous and Peek - Script

Wed, 24 Aug 2011 10:41:23 GMT

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

Re: Previous and Peek - Script

Wed, 24 Aug 2011 10:49:59 GMT

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

Re: Previous and Peek - Script

Wed, 24 Aug 2011 11:19:23 GMT

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;

Re: Previous and Peek - Script

Thu, 01 Sep 2011 07:45:27 GMT

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;