Can anyone help me understand why the Distinct isn't working in my script on sense.
The clients may of had several contacts with the same person in a day and I only want to return one of these. In ordered to do this I've added a concatenate and then used Load Distinct.
When I do a count of DuplicateKey it showing that the same values are being returned and that the load distinct has not worked.
If I then modify the count to a count Distinct in the expression it then returns my expected values. I need to connect this data to several other tables so want to ensure that I've got the distinct working in my load script.
Capitalize(Trim(Num("date")&CICSID&client)) as DuplicateKey,
I can't post a data file because it contains customer data.
This is very likely to occur because you have concatenated, so it adds another row to your table and will not automatically be the same record, of course DISTINCT will have no effect.
Maybe I did not understand exactly what you're doing, it would be nice if you could show the tables, what's coming back, and what you'd like returned. That way I could help you more.
Thank you for your replies. Nagesh Setty has hit the nail on the head with the time field. Commenting this out has worked.
Would love to be able to post tables as they clearly help in these discussions. Unfortunately I work in a heavily regulated. If I were to post any tables based on the data I could potentially be breaking at least 3 different laws, understandably not something I'm wanting to do.
I really do appreciate the support from the community.
Thank you Nagesh. This has resolved the issues I was having. The actions only let me mark your answer as helpful rather than correct but you have solved my problem.