topic Re: avoid repetition by selecting situationDate in QlikView

avoid repetition by selecting situationDate

aminehaddar — Wed, 25 Nov 2020 16:16:04 GMT

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:

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?

Re: avoid repetition by selecting situationDate

Anil_Babu_Samineni — Thu, 04 Jan 2018 14:08:51 GMT

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;

Re: avoid repetition by selecting situationDate

sunny_talwar — Thu, 04 Jan 2018 14:20:16 GMT

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;

Re: avoid repetition by selecting situationDate

Anil_Babu_Samineni — Thu, 04 Jan 2018 14:28:32 GMT

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?

Re: avoid repetition by selecting situationDate

sunny_talwar — Thu, 04 Jan 2018 14:31:13 GMT

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