Distinct not working in load script

[simonbowers]

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.

LOAD Distinct

  Capitalize(Trim(Num("date")&CICSID&client)) as DuplicateKey,

    "date",

    "time",

    CICSID,

    client,

    POLREF,

    Product,

    transaction,

    surname,

    postcode

From[*.xlsx]

I can't post a data file because it contains customer data.

Many thanks.

[PabloTrevisan]

Hi Simon!

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.

[simonbowers]

Thanks for the answer Pablo. How then do I get around this? I need the concatenate to understand which fields are the duplicates and it has worked in my other scripts.

[PabloTrevisan]

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.

[nsetty · Accepted solution]

I feel, "time" field would have been different for various records and hence you are not getting distinct rows.

Can you try commenting "time" field.....

[simonbowers]

Hi Pablo,

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.

[simonbowers]

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.

[priyalvp24]

Hi,

You can use the previous function.

If(Fieldname<>PREVIOUS(Fieldname) ,1,0) as Distinct,

Use Distinct=1 in your expression.

Thanks.