topic Re: Using Peek() and Previous() duplicate rows in App Development

Using Peek() and Previous() duplicate rows

dana — Tue, 07 Mar 2023 16:44:40 GMT

Hi People,

I have these transactions for a certain car.

If the date is identical for different locations (marked in red), I would like to update a counter, using it later as a coefficient.

CarKey	LocationCode	TMP_Station_StartDateTime	Station_EndDateTime	TransNo
20-CARE013941	53	02/06/2022 00:00:00	02/06/2022 00:00:00	708253
20-CARE013941	14	02/06/2022 00:00:00	01/07/2022 00:00:00	708963
20-CARE013941	888	01/07/2022 00:00:00	01/07/2022 00:00:00	737098
20-CARE013941	53	01/07/2022 00:00:00	28/07/2022 00:00:00	737116
20-CARE013941	999	28/07/2022 00:00:00	28/07/2022 00:00:00	750255
20-CARE013941	55	28/07/2022 00:00:00	01/08/2022 00:00:00	750257
20-CARE013941	55	01/08/2022 00:00:00	19/08/2022 00:00:00	751463

Here is my script:

LOAD
CarKey,
TransNo,
TMP_Station_StartDateTime,
Station_EndDateTime,
LocationCode,
if(CarKey = Previous(CarKey)
and LocationCode <> Previous(LocationCode)
and TMP_Station_StartDateTime = Previous(TMP_Station_StartDateTime), Peek('Counter') + 1,0) As Counter
Resident TMP_LedgerEntry
Order By CarKey,TransNo;

The result I get is duplicates for rows with identical dates:

Any suggestions for fixing it?

Thanks!

Re: Using Peek() and Previous() duplicate rows

rwunderlich — Tue, 07 Mar 2023 17:45:27 GMT

I think something may be missing in your example. Using the data and example exactly as you've posted, I don't see additional rows. Are there other tables in your data model that may be contributing rows to your chart?

For example, no duplicate rows from this script:

TMP_LedgerEntry:
LOAD CarKey,
   LocationCode,
   TMP_Station_StartDateTime,
   Station_EndDateTime,
   TransNo
FROM
[https://community.qlik.com/t5/App-Development/Using-Peek-and-Previous-duplicate-rows/td-p/2046340]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Counted:
LOAD
CarKey,
TransNo,
TMP_Station_StartDateTime,
Station_EndDateTime,
LocationCode,
if(CarKey = Previous(CarKey)
and LocationCode <> Previous(LocationCode)
and TMP_Station_StartDateTime = Previous(TMP_Station_StartDateTime), Peek('Counter') + 1,0) As Counter
Resident TMP_LedgerEntry
Order By CarKey,TransNo;

Re: Using Peek() and Previous() duplicate rows

dana — Tue, 07 Mar 2023 17:59:25 GMT

Hi Rob,

You nailed it!

It was a single table with duplicate records.. exporting to Excel of course did not "reveal" the issue.

Thanks for helping me to resolve this!