Showing unique values only

[Anonymous]

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?

[devarasu07]

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;

[woshua5550]

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

[madhumitha]

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!

[Anonymous]

Hello and thanks for your answer,

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

[Anonymous]

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.

[effinty2112]

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

[Anonymous]

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.

[Anonymous]

I fixed it with

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