topic Help getting distinct column in straight table in QlikView

Help getting distinct column in straight table

meckeard — Wed, 25 Nov 2020 16:16:04 GMT

I need help getting a distinct customer age in to a column of a straight table. Some customers have 2 records due to having 2 different ages. All other fields in the record is the same. It's due to bad data and I can't change that, so I need to get either age as accuracy isn't important.

Here are the tables in my schema and I'm excluding non-relevant fields:

Customer:
Cust_Nbr
Cust_Age

Accounts:
Cust_Nbr
AccountNumber

Losses:
AccountNumber

The issue is when I add the field Cust_Age to my straight table. That puts multiple records in my straight table and I need only 1. I'd prefer to do this in the table, not the load script. I've tried adding a dimension and expression using avg, aggr and distinct but none work. What can I do?

Thanks!

Re: Help getting distinct column in straight table

DavidM — Thu, 12 Sep 2019 16:17:56 GMT

You can change it either in script editor or in frontend. Either load distinct, or min or max.

If you can't make changes in script editor, then use age as measure and as formula use either min or max (or average, doesn't matter):

Max(Cust_Age)

Re: Help getting distinct column in straight table

meckeard — Thu, 12 Sep 2019 16:27:37 GMT

DavidM,

That worked as an expression but returned all accountnumber records, even those without a customer record. However, I remembered the "suppress when value is null" option, selected it and that worked.

New question - can I promote the expression within the group of dimensions? I'm guessing no and that's why I wanted this to be a dimension so I could move it to the front of the list of columns.

Re: Help getting distinct column in straight table

DavidM — Fri, 13 Sep 2019 07:49:21 GMT

Do you mean that you want to put the age as first column in the table? You can just drag & drop it in the front.

I am afraid, there is no way to show just one value as long as it dimension (I am guessing fixing this in script is not an option for you).