Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
fadichmn
New Contributor II

Showing unique values only

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 NODESCRIPTIONLOCATIONLASTORDER
1Article 1102017-10-31
1Article 1112017-10-31
2Article 2202017-07-25
2Article 2212017-07-25
3Article 3302017-10-31
3Article 331

2017-10-31

4Unique Article 4322017-10-31

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

Tags (2)
8 Replies
Highlighted
devarasu07
Honored Contributor II

Re: Showing unique values only

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;

Highlighted
woshua5550
Contributor III

Re: Showing unique values only

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

Highlighted
madhumitha
Contributor

Re: Showing unique values only

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!

Highlighted
fadichmn
New Contributor II

Re: Showing unique values only

Hello and thanks for your answer,

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

qlikview-sql-unique article.png

Highlighted
fadichmn
New Contributor II

Re: Showing unique values only

How I want to solve this is something like this:

  1. Article is in Location 1
  2. Location 1 is broken because of service
  3. I choose the location in QV
  4. The target location will show me the unique articles that isnt in the other locations and there is only in location 1.
  5. Hide all other articles.
Highlighted
effinty2112
Honored Contributor

Re: Showing unique values only

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

fadichmn
New Contributor II

Re: Showing unique values only

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

  1. Article is in Location 1
  2. Location 1 is broken because of service
  3. I choose the location in QV
  4. The target location will show me the unique articles that isnt in the other locations and there is only in location 1.
  5. Hide all other articles.
Highlighted
fadichmn
New Contributor II

Re: Showing unique values only

I fixed it with

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