Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
alsellaro
Contributor III
Contributor III

Odd Peek/Previous behavior

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:

dateclienttarget
04/22/2018123A
04/22/2018345A
04/23/2018123B
04/23/2018345A

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:

dateclienttargetchange
04/22/2018123ANo
04/22/2018345ANo
04/23/2018123BA
04/23/2018345ANo

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.

1 Solution

Accepted Solutions
sunny_talwar

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.

View solution in original post

14 Replies
sunny_talwar

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;

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
alsellaro
Contributor III
Contributor III
Author

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.

alsellaro
Contributor III
Contributor III
Author

Sunny, thanks for your answer.


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

sunny_talwar

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

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
alsellaro
Contributor III
Contributor III
Author

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.

alsellaro
Contributor III
Contributor III
Author

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

sunny_talwar

Share some more data where we can replicate the issue?