avoid repetition by selecting situationDate

[aminehaddar]

Hello the community,

I have 12 files .txt. Every file containts the data of a particular month.

All the files have the same structure:  ID , Date_situation .

I need to load all of them and store the result in a new file.

my script:

X:

LOAD ID,

     '31-01-2017' as Date_Situation

FROM

[...\f1.TXT]

(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

LOAD ID,

     '28-02-2017' as Date_Situation

FROM

[.../f2.TXT]

(txt, codepage is 1252, embedded labels, delimiter is '|', no quotes);

as a result, I got repetition of the majority of ID , which is logic. cause in the majority of the files we did a monthly update of some ID.

The objectif is to select all the ID, if there is a repetition, I should select only the last update.

Anyone can help please?

[Anil_Babu_Samineni]

How to know which is Last Update? Perhaps club this to your multi tables?

Right Join (X)

Load Max(Date_Situation) as Date_Situation

Resident X;

[sunny_talwar]

I think you need to Group by ID here to make

Right Join (X)

Load ID,

    Max(Date_Situation) as Date_Situation

Resident X

Group By ID;

[Anil_Babu_Samineni]

May be, But once upon a time i tried without Group By seems working. Because Right Join Don't required to use Group By for Unique Rows. May be need when Non-Unique rows only. Correct me if i am wrong?

[sunny_talwar]

Within ID, you will find the max date across all IDs... I think (correct me if you think I am wrong), but OP wants to keep the max date data for each ID..... If that is the case, not adding ID to the Right Join table can potential drop IDs