Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
Field names in Qlik Sense are case sensitive. That means Target and target are two different fields.
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.
Sunny, thanks for your answer.
I added your suggestions, but the Peek('target') is still returning an empty string/NULL.
Would you be able to share a qvw sample to show us the issue?
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;
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.
Sunny, unfortunately I am not able to extract/upload qvw files.
Share some more data where we can replicate the issue?