topic Re: don't want distinct in QlikView

don't want distinct

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

Hi All,

Below is my data when iam doing sum(col1) it is missing last record (28800), how to count All values

these values are cumming from QVD

Col1	Col2
Assignee	252000
Assignee	144000
Assignee	115200
Assignee	57600
Assignee	28800
Assignee	28800

It show's in qlikview as below , need same as above

Col1	Col2
Assignee	252000
Assignee	144000
Assignee	115200
Assignee	57600
Assignee	28800
Assignee	-

Regards,

Nagesh

Re: don't want distinct

prat1507 — Wed, 15 Feb 2017 04:41:55 GMT

You may have a problem with data load. Please check. I'm getting the desired results. PFA.

Regards

Pratyush

Re: don't want distinct

eromiyasimon — Wed, 15 Feb 2017 04:43:20 GMT

how can you sum col1 which is containing text value

attach sample QVW

Re: don't want distinct

vinieme12 — Wed, 15 Feb 2017 05:17:41 GMT

can you post the app that demonstrates the problem? are you doing this with an inline table load?

Re: don't want distinct

rahulpawarb — Wed, 15 Feb 2017 05:46:58 GMT

Hello Nagesh,

You can apply Count function on Col1 as well as Col2; however, Sum function can only be applied on Numeric values.

As per my primary analysis, last record (28800) is not missing; it is there in application. 2nd last and last record have same values; due to this QlikView is interpreting it as single combination.

If you create the straight table with Col1 & Col2 as dimensions and Count(Col1) & Sum(Col2) then you will notice that last record is there. Refer attached sample application.

Also, to get distinct values you can make use of RowNo() or RecNo() function (refer below given sample script).

Data:
LOAD *,
RowNo() AS RowNo,
RecNo() AS RecNo;
LOAD * INLINE [
Col1, Col2
Assignee, 252000
Assignee, 144000
Assignee, 115200
Assignee, 57600
Assignee, 28800
Assignee, 28800
];

Regards!

Rahul

Re: don't want distinct

sdmech81 — Wed, 15 Feb 2017 05:57:47 GMT

HI,

R u doing sum(col2)..It should give u desired result..

Plss attach sample qvw..

Sachin

Re: don't want distinct

Wed, 15 Feb 2017 05:58:21 GMT

Hi Nagesh,

please share your sample application so that we will help to you.

Re: don't want distinct

Wed, 15 Feb 2017 06:02:09 GMT

try like this

count(nodistinct col1)