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

Question

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 Answer

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

Sign up

Login with SSO

Login to the community

Login with SSO