Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
New navigation header in progress. Thank you for your patience: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
simonbowers
Contributor III
Contributor III

Distinct not working in load script

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.

1 Solution

Accepted Solutions
nsetty
Partner - Creator II
Partner - Creator II

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.....

View solution in original post

7 Replies
PabloTrevisan
Partner - Creator II
Partner - Creator II

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
Contributor III
Contributor III
Author

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
Partner - Creator II
Partner - Creator II

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
Partner - Creator II
Partner - Creator II

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Creator
Creator

Hi,

You can use the previous function.

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

Use Distinct=1 in your expression.

Thanks.