topic Re: How to add expression in straight table without duplicates? in QlikView

How to add expression in straight table without duplicates?

Tue, 12 Aug 2014 15:00:00 GMT

Hi,

I have a column CustomerId with duplicates. I need to add this column as expression in straight table without duplicates. can anyone please suggest how to add expression without duplicates.

Thanks.

Re: How to add expression in straight table without duplicates?

Anonymous — Tue, 12 Aug 2014 15:02:46 GMT

It is possible.

After adding the CustomerID what do you want to do in straight table?

Re: How to add expression in straight table without duplicates?

Tue, 12 Aug 2014 15:19:34 GMT

Thanks. I have to add multiple columns (name,Id,progress) in the table as dimensions. but the expression is Count(Distinct(IF(ISNULL(DateIclosed),ID)))

here Id have unique values only Customerid have duplicates. But I need both ID and Customerid to add in table.

Please help.

Re: How to add expression in straight table without duplicates?

sebastiandperei — Tue, 12 Aug 2014 15:24:37 GMT

Hi, could you send a reduced copy?

Re: How to add expression in straight table without duplicates?

Tue, 12 Aug 2014 15:41:24 GMT

Thanks. Please see attached.

Re: How to add expression in straight table without duplicates?

Anonymous — Tue, 12 Aug 2014 15:49:19 GMT

I checked your data.

Lets take one example.

The customer ID 39 has two different IDs(11108,26218).

In this scenario how do you want to see the output?

Re: How to add expression in straight table without duplicates?

Tue, 12 Aug 2014 15:59:34 GMT

Thanks. I want to see the straight table should show only one CustomerID with two IDs(11108,26218).

i,e the CustomerID should populate only once in straight table.

Is it possible?

Re: How to add expression in straight table without duplicates?

Tue, 12 Aug 2014 16:01:51 GMT

Try using Only (DISTINCT [Customer ID])

Re: How to add expression in straight table without duplicates?

sebastiandperei — Tue, 12 Aug 2014 16:19:15 GMT

For CustomerID=39, you have ID=26218 and ID=11108 Which one you want to see? They have different values of DataClose, DataEnter and Name field values.

Your expression will count the "ID" that have not null in "DataClosed" field, so if you put the "ID" dimmention, obviously, the number of ID for each ID will always return 1.

Remember: The expression will calculate for each combination of the different dimmentions. What exactly do you need?

Re: How to add expression in straight table without duplicates?

Wed, 13 Aug 2014 08:39:21 GMT

Thanks. It is showing error

Re: Re: How to add expression in straight table without duplicates?

tresB — Wed, 13 Aug 2014 08:51:30 GMT

Like attached sample?

Re: How to add expression in straight table without duplicates?

kaushiknsolanki — Wed, 13 Aug 2014 08:52:10 GMT

Hi,

Its not possible to do this with straight table, instead you can use the pivot table.

Regards,

Kaushik Solanki

Re: How to add expression in straight table without duplicates?

rupaliqlik — Wed, 13 Aug 2014 09:04:25 GMT

please use Pivot table to show

Re: How to add expression in straight table without duplicates?

Wed, 13 Aug 2014 09:18:18 GMT

Thanks very much.

Re: How to add expression in straight table without duplicates?

sebastiandperei — Wed, 13 Aug 2014 11:36:34 GMT

Kaushik, sorry, it is possible to do it in a straight table.

You just need to know, in this example, which ID you want to see, and use AGGR to aggregate all results in one by Customer ID.

Amelia, I was asking you this, and you said "Thanks"!!!

Re: How to add expression in straight table without duplicates?

Wed, 13 Aug 2014 12:49:19 GMT

Sorry. I mentioned in my previous tab (Aug 12, 2014 11:59 AM) ,as I need both IDs to have in the table. Can you send me please how to use AGGR?

Re: How to add expression in straight table without duplicates?

Wed, 13 Aug 2014 13:49:31 GMT

Sorry. when I am sending to excel it is showing again multiple instances of CustomerID where I want to see only one Instance with IDs. I have used pivot table. can sort my problem please!

Re: How to add expression in straight table without duplicates?

Mon, 18 Aug 2014 14:07:47 GMT

Thanks. when I am using pivot table again the values are showing as duplicates when exporting to excel. Please help me.

Re: Re: How to add expression in straight table without duplicates?

sebastiandperei — Wed, 20 Aug 2014 01:42:14 GMT

I cant understand...... sorry.

For example, about CustomerID 39, you have:

CustomerID	ID	Dateclosed	Dateenter	name	Count
					2
39	26218	09/05/2014	16/04/2014	mor	1
39	11108	14/05/2014	28/11/2013	add	1

You said that you DONT WANT to see Files with duplicated values of CustomerID. Then, which file you DONT WANT to see?

You have TWO different ID for one CustomerID, so you should say which one "ID" you would like to see, because it is the only way to see only ONE file, if you want to see the ID.

Could you understand what we need to get the way to help you?

First think what you need, without think about Qlikview, and send a table with the values you need to show, please.

Re: Re: How to add expression in straight table without duplicates?

jagan — Wed, 20 Aug 2014 02:40:03 GMT

Hi Amelia,

PFA attached file for solution. created one dummy expression for Customer ID and hidden the customer dimension in presentation tab.

Hope this helps you.

Regards,

Jagan.