Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
try get rid of "LOCATION" ,because it's different row by row
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!
Hello and thanks for your answer,
This is my tree layout. This is where I am getting my data (ID or Location) from
How I want to solve this is something like this:
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
I tried that too but it just gave me the same results.
I fixed it with
COUNT(DISTINCT SUBSTR(LOC.NAME,1,6)) OVER (PARTITION BY M.EXTERNALIDENTIFIER) AS SRM_USED