Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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;
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?
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