Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
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.

Anonymous
Not applicable
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
Anonymous
Not applicable
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.

Anonymous
Not applicable
Author

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

sunny_talwar

Share some more data where we can replicate the issue?