Odd Peek/Previous behavior

[Anonymous]

I have a data table that receives daily data. I need to show which records changed a specific field as compared to the previous day. I have tried to use both Peek and Previous, but I am getting NULL results, which doesn't make any sense. Can you please help me figure out what I am doing wrong?

The simplified source data table looks like this:

date	client	target
04/22/2018	123	A
04/22/2018	345	A
04/23/2018	123	B
04/23/2018	345	A

During the data loading, I am creating a new field called Change that should list the previous value for Target or No if there was a change from the previous date for any given client. For the above example, the resulting table would be:

date	client	target	change
04/22/2018	123	A	No
04/22/2018	345	A	No
04/23/2018	123	B	A
04/23/2018	345	A	No

The loading script I have tried is below. The Change field on the resulting table has only NULL values. I have no idea why.

[temp_table_daily]:
LOAD * FROM [lib://temp_table_daily.qvd]
(qvd);

[table_daily_final]:
REPLACE LOAD

date,
client,
target,
if(Peek('target') <> target, Peek('target'), 'No') AS change

RESIDENT temp_table_daily
WHERE [target] <> ('') and IsNull([target]) <> -1 and date > ('4/1/2018')
ORDER BY [client] ASC, [date] ASC;

DROP TABLE temp_table_daily;

I have also tried the script above but changing Peek by Previous. Same odd result.

Thanks in advance.

[Qrishna]

try this -

code:

data:

load *,

if( 

   (client <> previous(client) and client = peek(client,1) and target = peek(target,1)) or

    RowNo()=1 or

    RowNo()=2 ,

   'No', peek(target,1)

   )as change

;

load * inline [

date,client,target

04/22/2018, 123, A

04/22/2018, 345, A

04/23/2018, 123, B

04/23/2018, 345, A

]

[Anonymous]

Apparently, what made the trick was this comment from another thread: Re: Get previous row value when the value is null

For some reason, the Previous/Peek did not get the right previous rows during an Ordered By Load. The solution was:

1. Load the data from a QVD into a Temp table 1;
2. Resident load Temp table 1 into a temp table 2 ordering it;
3. Then use Previous/Peak taking table 2 as the resident data source for my final table.

Although this approach solved the problem, it seems that Previous/Peek indeed don't work well together with a LOAD... ORDER BY clause. This seem to me as a bug, but I might be squarely wrong as well.

Thanks for all the help.

[sunny_talwar · Accepted solution]

Although this approach solved the problem, it seems that Previous/Peek indeed don't work well together with a LOAD... ORDER BY clause. This seem to me as a bug, but I might be squarely wrong as well.

I don't think this is a right claim... I think the issue might be because of the where statement, but I am not entirely sure... did you move the the where statement to step number 2 also? or was it part of 3? If it was part of 2, try moving it to 3 and I think you will see the same behavior you were seeing earlier.

[Anonymous]

You're absolutely right, Sunny. The issue returns if I move the WHERE clause to the step 3. So, it seems that whatever manipulation you make during LOAD, be it ORDER BY or a WHERE, you should do on temp tables and use the Previous/Peek only with a "clean" LOAD (i.e. no ORDER BY and no WHERE).

Thanks for catching this.

[sunny_talwar]

I don't think the issue is related to Order By... I think Order by will always work... I think the issue is related to Peek()/Previous() where one is operates on the output table (Peek()) and the other on the input table (Previous())... I am not entirely sure what might be going on... but in my 3+ years of using Peek/Previous with Order By have never given me any issues. But have never tried this with the where statement....