topic Showing unique values only in QlikView

Showing unique values only

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

Hello,

I am stuck here and I tried with DISTINCT, but I cannot get it done.

This is the script:

Article:
LOAD *,
RowNo() as "ROW_Article",
DAY(LASTORDER)      AS "DAY",
MONTH(LASTORDER)    AS "MONTH", 
YEAR(LASTORDER)      AS "YEAR"
;
SQL
SELECT Distinct
ID AS ARTICLEID,
DESCRIPTION,
EXTERNALIDENTIFIER AS ARTICLENO,
LASTORDER
FROM MATERIAL
;

This is the table:

ARTICLE NO	DESCRIPTION	LOCATION	LASTORDER
1	Article 1	10	2017-10-31
1	Article 1	11	2017-10-31
2	Article 2	20	2017-07-25
2	Article 2	21	2017-07-25
3	Article 3	30	2017-10-31
3	Article 3	31	2017-10-31
4	Unique Article 4	32	2017-10-31

What I want is to show only article 4 because of its a unique article from the others. Any Idea how?

Re: Showing unique values only

devarasu07 — Wed, 01 Nov 2017 08:34:44 GMT

Hi,

location or ID causing the issue.

may try like this or remove the location field from your query.

Article:

LOAD *,

RowNo() as "ROW_Article",

DAY(LASTORDER) AS "DAY",

MONTH(LASTORDER) AS "MONTH",

YEAR(LASTORDER) AS "YEAR" ;

SQL

SELECT Distinct

DESCRIPTION,

EXTERNALIDENTIFIER AS ARTICLENO,

LASTORDER

FROM MATERIAL;

Re: Showing unique values only

woshua5550 — Wed, 01 Nov 2017 08:37:05 GMT

try get rid of "LOCATION" ,because it's different row by row

Re: Showing unique values only

madhumitha — Wed, 01 Nov 2017 08:52:03 GMT

Hello Fadi,

You have use "count(Description)=1 " in the where clause, with the group by of all the other fields.

Distinct would just reduce the duplicates and does not give you the one time occurance.

Thanks!

Re: Showing unique values only

Anonymous — Wed, 01 Nov 2017 09:27:12 GMT

Hello and thanks for your answer,

This is my tree layout. This is where I am getting my data (ID or Location) from

Re: Showing unique values only

Anonymous — Wed, 01 Nov 2017 09:37:27 GMT

How I want to solve this is something like this:

Article is in Location 1
Location 1 is broken because of service
I choose the location in QV
The target location will show me the unique articles that isnt in the other locations and there is only in location 1.
Hide all other articles.

Re: Showing unique values only

effinty2112 — Wed, 01 Nov 2017 09:39:00 GMT

Hi Fadi,

Maybe something like this after loading your article table

TempTable:

Load

ARTICLEID,

Count(ARTICLEID) as Cnt

Resident Article Group by ARTICLEID;

Inner Join(Article)

Load ARTICLEID Resident TempTable WHERE Cnt = 1;

DROP TABLE TempTable;

Good luck

Andrew

Re: Showing unique values only

Anonymous — Wed, 01 Nov 2017 09:56:42 GMT

I tried that too but it just gave me the same results.

Article is in Location 1
Location 1 is broken because of service
I choose the location in QV
The target location will show me the unique articles that isnt in the other locations and there is only in location 1.
Hide all other articles.

Re: Showing unique values only

Anonymous — Fri, 29 Dec 2017 07:06:51 GMT

I fixed it with

COUNT(DISTINCT SUBSTR(LOC.NAME,1,6)) OVER (PARTITION BY M.EXTERNALIDENTIFIER) AS SRM_USED