Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Problem using Distinct

Hi people,

For some reason my distinct doesnt work.

TMP:

LOAD

TIMESTAMP([Entrada Desenv.],'MM/YYYY') AS DATA_SELECAO

RESIDENT AVALIACAO_ANALISE

;

DATA_SELECAO:

LOAD

distinct

DATA_SELECAO

RESIDENT TMP;

DROP TABLE TMP;

Without Distinct, it works fine

Erro_Distinct.JPG

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Why not just using:

DATA_SELECAO:

LOAD date(floor(monthstart([Entrada Desenv.])),'MM/YYYY') AS DATA_SELECAO

RESIDENT AVALIACAO_ANALISE;

DATA_SELECAO:

LOAD distinct DATA_SELECAO RESIDENT TMP;

DROP TABLE TMP;

with adjusting your YearMonth field because timestamp() will only format a field but it willbe remain a timestamp.

- Marcus

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Why not just using:

DATA_SELECAO:

LOAD date(floor(monthstart([Entrada Desenv.])),'MM/YYYY') AS DATA_SELECAO

RESIDENT AVALIACAO_ANALISE;

DATA_SELECAO:

LOAD distinct DATA_SELECAO RESIDENT TMP;

DROP TABLE TMP;

with adjusting your YearMonth field because timestamp() will only format a field but it willbe remain a timestamp.

- Marcus

View solution in original post

Highlighted
Specialist II
Specialist II

Hi Marcus !

It works and i think what's happen now.

A Timestamp is just visual. The day of my date field still there even if i use a timestamp MMYYY and for that reason distinct doesnt works. Right?

Highlighted
Creator III
Creator III

you need to use function floor like Marcus show you other wise it will keep all valur in a field (for example date as timestamp is save as 41909.987 and even if it will show only 27/09/2014 it will still save as '41909.987'and date for example 41908.087 will be as other date even if is 26/09/2014 as well. even if its the same month

you can use as well function MonthName

TMP:

LOAD

MonthName(date([Entrada Desenv.])) AS DATA_SELECAO

RESIDENT AVALIACAO_ANALISE

Highlighted
MVP & Luminary
MVP & Luminary

Normally the distinct keyword itself shouldn't lead to an error in this case but within your first approach there shouldn't be a table DATA_SELECAO.

The reason for this is that this table has the same data-structure like the table TMP and will be automatically concateneted with TMP and afterwards TMP is dropped - so that there would be no DATA_SELECAO and TMP tables within the datamodel. And this might be your displayed error - because the field for it is missing.

- Marcus

Highlighted
Specialist II
Specialist II

Thank you Ana, I was wondering that after Marcus explanation and you reinforced this.