topic Re: How to display extra record? in QlikView

How to display extra record?

Sat, 10 May 2014 07:29:28 GMT

Hi All,

I have a field with unique, but it has more than one record, there i need to display how many more than one records or there for that field.

Re: How to display extra record?

mambi — Sat, 10 May 2014 09:44:18 GMT

=count(distinct YourFieldName)

Re: How to display extra record?

parthiband — Sat, 10 May 2014 09:49:26 GMT

can u please explain a bit?

Because, Count (DISTINCT FieldName) will always return the unique records (Text Object).

But if u want in some other object it might vary.

So explain, in which object/place u want to showcase the distinct values and in what case.

Thanks,

Re: How to display extra record?

mambi — Sat, 10 May 2014 09:54:36 GMT

Count (DISTINCT FieldName) works also on other objects like charts ...

if you've an exemple it's will be easier to explain

Re: How to display extra record?

its_anandrjs — Sat, 10 May 2014 09:58:46 GMT

Elaborate more about your query

And Suppose you count any field and there are multiple records then in a straight table take dimension and Count(Dimension)

Eg:-

Dim:- ID

Expr:-Count(ID)

Expr:- Count(DISTINCT ID) //For unique records

Re: How to display extra record?

parthiband — Sat, 10 May 2014 09:59:36 GMT

I think, you may be wrong in cases like, you have a dimensions in straight table with ur field name and dates(Attendance date/ Order dates).

It cannot be unique.

The ur field might be applicable for both dates, right?

So john, its better if u explain us a bit.

Re: How to display extra record?

mambi — Sat, 10 May 2014 10:03:10 GMT

expressions can't be used with straight table

Re: How to display extra record?

PrashantSangle — Sat, 10 May 2014 10:11:42 GMT

Hi,

If you want show count of all records from same List Box

and want to show count of all records other than current selection then use

Indirect Set analysis Like

=count(DISTINCT {<FieldName=e(FieldName)>}FieldName)

Or if you want to show count of Other List Box Depend on current selection of List box

Then use

=Count (DISTINCT FieldName)

Regards,

Re: How to display extra record?

maxgro — Sat, 10 May 2014 11:01:43 GMT

if I understand your question

sum(if( aggr(count(field),field)>1,1))

Re: How to display extra record?

Sat, 10 May 2014 11:37:50 GMT

Thank you Mambi Badi. count(distinct YourFieldName) gives you Distict records count. but what i want the records which are extra for these field atrributes.

ex: id value

101 axe

101 box

101 car

102 pen

103 axe

102 car

out put: i am expecting: id value

101 box

101 car

102 car.

Re: How to display extra record?

swuehl — Sat, 10 May 2014 11:46:44 GMT

It would be most easy to create a duplicate flag in the load script:

LOAD *,

if(exists(id),1,0) as Flag

INLINE [

id, value

101, axe

101, box

101, car

102, pen

103, axe

102, car

];

Then create a list box with Flag field and a table box with fields id and value. Select 1 in Flag list box. Done.

Re: How to display extra record?

Sat, 10 May 2014 11:48:39 GMT

Hi Parthiban

Thank you for replay, just go through this

id value

101 axe

101 box

101 car

102 pen

103 axe

102 car

Here 101 has 2 more records same time 102 has 1 record more so i want to display the count of more record in this case it is 2

and i want those two records .

out put: i am expecting: id value

101 box

101 car

102 car

Hope its clear to you..

Re: How to display extra record?

swuehl — Sat, 10 May 2014 11:50:20 GMT

In addition to my above post, if you want to show the number of duplicates, just create a straight table chart with dimension id and as expression

=sum(Flag)

Re: How to display extra record?

Sat, 10 May 2014 11:52:45 GMT

Hi Massimo Grossi

Thank you for replay, just go through this

id value

101 axe

101 box

101 car

102 pen

103 axe

102 car

Here 101 has 2 more records same time 102 has 1 record more so i want to display the count of more record in this case it is 2

and i want those two records .

out put: i am expecting: id value

101 box

101 car

102 car

Hope its clear to you..

Thank You

Re: How to display extra record?

Sat, 10 May 2014 12:00:59 GMT

swuehl thank you for the replay

it shows only the count but i need those records .

Re: How to display extra record?

swuehl — Sat, 10 May 2014 12:06:35 GMT

It will show the records if you follow my previous post.

You can also create a straight table with dimensions id and value and expression

=sum(Flag)

Then hide the expression in presentation tab.

Re: How to display extra record?

Sat, 10 May 2014 12:20:32 GMT

Thank you so much...SWUEHL

Re: How to display extra record?

Thu, 15 May 2014 15:35:21 GMT

This is my data and i need the difference value records from each table according to position

Table1:

Customer	Contract	position	part	company
1212	101	working	12	Bmw
1215	101	repaired	13	skoda
1254	102	unused	12	Audy
1254	103	working	13	Bmw
1212	105	repaired	41	skoda
1215	106	unused	42	Audy
1211	107	working	12	Bmw

Table2:

Customer	Contract	position	part	company	date
1212	101	working	12	Bmw	1/12/2011
1215	101	repaired	13	skoda	2/12/2012
1254	102	unused	12	Audy	4/11/2010
1254	103	working	13	Bmw	3/6/2010
1212	105	repaired	41	skoda	6/4/2009
1215	106	unused	42	Audy	5/4/2008
1211	107	working	12	Bmw	4/9/2012
1211	108	working	12	Bmw	02/5/1/2013
12102	107	working	43	Audy	11/3/1911
1205	109	repaired	44	bmw	12/7/2011
1205	107	unused	46	skoda	11/3/1911
1208	109	working	47	bmw	11/4/1202

Here have struck what I have upto know

Position: Total contract in table1 Total contract in table2 difference

working 3 6 3

here difference is 3 , now i need those 3 different records from table 2.

Hope you understand Please Provide me some help to over come this. Thank you.