topic Re: Odd Peek/Previous behavior in App Development

Odd Peek/Previous behavior

Anonymous — Mon, 23 Apr 2018 17:31:41 GMT

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.

Re: Odd Peek/Previous behavior

sunny_talwar — Mon, 23 Apr 2018 17:40:21 GMT

Not sure what might be wrong, but here are few suggestions

[temp_table_daily]:

LOAD *

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

[table_daily_final]:

LOAD date,

client,

target,

If(client = Previous(client),

If(Peek('target') <> target, Peek('target'), 'No')) as change

RESIDENT temp_table_daily

WHERE Len(Trim([target])) > 0 and date > MakeDate(2018, 4, 1)

ORDER BY [client], [date];

DROP TABLE temp_table_daily;

Re: Odd Peek/Previous behavior

Gysbert_Wassenaar — Mon, 23 Apr 2018 17:40:41 GMT

Field names in Qlik Sense are case sensitive. That means Target and target are two different fields.

Re: Odd Peek/Previous behavior

Anonymous — Mon, 23 Apr 2018 17:42:39 GMT

Thanks, Gysbert. I just checked and my script is consistent with the field name. I updated the example above to reflect that. The odd behavior continues, though.

Re: Odd Peek/Previous behavior

Anonymous — Mon, 23 Apr 2018 17:55:13 GMT

Sunny, thanks for your answer.

I added your suggestions, but the Peek('target') is still returning an empty string/NULL.

Re: Odd Peek/Previous behavior

sunny_talwar — Mon, 23 Apr 2018 17:56:17 GMT

Would you be able to share a qvw sample to show us the issue?

Re: Odd Peek/Previous behavior

Gysbert_Wassenaar — Mon, 23 Apr 2018 17:58:36 GMT

Perhaps like this:

Input:

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

];

Result:

LOAD

date,

client,

target,

If(Previous(target)=target or RowNo()=1,'No',peek(target)) as change

RESIDENT

Input

ORDER BY

client,

date

;

DROP TABLE Input;

Re: Odd Peek/Previous behavior

Anonymous — Mon, 23 Apr 2018 18:19:17 GMT

Your example works. I have also managed to use Peek / Previous for other fields. Strangely, the same logic does not work if I use the target field. I double checked and there are previous records with non-NULL target values.

Re: Odd Peek/Previous behavior

Anonymous — Mon, 23 Apr 2018 18:19:55 GMT

Sunny, unfortunately I am not able to extract/upload qvw files.

Re: Odd Peek/Previous behavior

sunny_talwar — Mon, 23 Apr 2018 18:20:55 GMT

Share some more data where we can replicate the issue?

Re: Odd Peek/Previous behavior

Qrishna — Mon, 23 Apr 2018 18:33:12 GMT

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

]

Re: Odd Peek/Previous behavior

Anonymous — Tue, 24 Apr 2018 12:24:07 GMT

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:

Load the data from a QVD into a Temp table 1;
Resident load Temp table 1 into a temp table 2 ordering it;
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.

Re: Odd Peek/Previous behavior

sunny_talwar — Tue, 24 Apr 2018 12:27:53 GMT

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.

Re: Odd Peek/Previous behavior

Anonymous — Tue, 24 Apr 2018 13:19:53 GMT

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.

Re: Odd Peek/Previous behavior

sunny_talwar — Tue, 24 Apr 2018 13:23:13 GMT

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....