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.

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

[Anonymous]

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]

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;

[Anonymous]

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]

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

[sunny_talwar]

Share some more data where we can replicate the issue?