Problem using Distinct

[eduardo_dimperio]

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

[marcus_sommer · Accepted solution]

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

[eduardo_dimperio]

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?

[annafuksa1]

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

[marcus_sommer]

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

[eduardo_dimperio]

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