How to add a field that can generate unique value for each record using Query service

[ShivaniM]

Hi Experts,

We have created an adhoc dataset, which combines the records of all three system datasets from AJO: EMAIL, PUSH and MESSAGE.

But, as multiple joins were used in query, no field is unique in records.

Is there a way we can add a new coulmn which gets updated sequentially in this dataset with each record, so it is unique.

I checked with rand() function, but it has limit and rownumber() will start with 1 again every time the query runs.

Query is scheduled for every 24 hours.

Thanks!

Shivani

[Pradeep_Kumar_Srivastav · Accepted solution]

Hi @ShivaniM , you can use randn or UUID for generating unique identifiers.

Alternatively, you can try concatenating fields. For example, combine the timestamp (now()) with other relevant fields to create a more specific identifier, or use ROW_NUMBER() along with the current date.
Please refer https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/spark-sql-functions