Multiple date filters in one IF() statement

[AuroraA]

I want to create a new field in my script that returns the word "Termination" if there is a TermDate for an ID, but only for the first date in ChangeDate where there is a TermDate. If an ID has more than one TermDate and they are different, the field should return more than one Termination for that ID. Probably confusing, so here is my sample data (based on my database):

This is what I need the new field to look like:

where there is a TermDate, Termination will only return on the earliest date in the ChangeDate field. If there are two different TermDates for one ID, it'll return Termination once for each date but only for that earliest ChangeDate. 

I hope this makes sense, and someone can help me with this.

[G3S]

calculate the min of change and term dates, grouped by ID. left join into your table. 

then if (min change date = change date and min term date= term date, 'your text', '')

I believe this would work.

[AuroraA]

The data has multiple term dates and some of them are the exact same, but the change dates for these are different so that’s where I need to use your suggestion of min. But if I was to use the min of term date, it would return my text next to the earliest term dates that are all the same for an ID and it would ignore if there is another term date that needs that text next to it. This is what that would return:

This is what I need it to return:

Where it returns my text if there is a term date, it’s different than other term dates, and it is only next to the min change date for that specific term date.

I hope this helps to further explain what I am looking for

[G3S]

idea is that the lowest row id for ID&TermDate combination is tagged with 'Termination'.

generate row # for each record (there's a function, I cannot recall, please look it up). create key with : ID&TermDate as Key. 

calculate the count (Key), len(Key),min(rowID)

group this Key1 and left join to your data. then try this if statement:  

if(KeyCount=1 and KeyLength>5, 'Termination',

if(KeyCount>1 and KeyLength>5 and MinRowID=RowID, 'Termination')) as Yourflag

I am using key length as 5 cos I took the '-' in TermDate to be a dash. if it is null, please change to 4 (or the length of your ID)

[PrashantSangle]

Can you provide your sample data in csv? It is possible only in backend(script). In front end looks difficult for me.

[Aditya_Chitale]

@AuroraA ,

You can use below method to show the flag using frontend. For this you will have to write a small if() statement in the backend. Other part can be done using frontend.

Step 1 (Backend Part) : Assign common row numbers based on same IDs and exclude null Term Date values:

=if(len(trim(TermDate))=0,null(), AutoNumber(ID)) as rowno;

Step 2: Now using firstsortedvalue() function with Aggr() create a new measure column containing earliest date with non-null term date values:

=Aggr(nodistinct FirstSortedValue(ChangeDate,ChangeDate),ID,rowno)

Step 3: compare existing changedate column with newly created smallest date column (from step 2) and calculate flag.

=if(ChangeDate = Aggr(nodistinct FirstSortedValue(ChangeDate,ChangeDate),ID,rowno) and len(trim(TermDate))<>0, 'Termination')

Sample data used:

test:

load *,

if(len(trim(TermDate))=0,null(), AutoNumber(ID)) as rowno;

LOAD * Inline

[

ID, Name, ChangeDate, TermDate

5072, John, 1/5/2021,

5072, John, 9/12/2021, 9/11/2021

5072, John, 9/6/2021, 9/11/2021

7583, Anna, 1/8/2021,

7583, Anna, 3/31/2022, 4/1/2022

7583, Anna, 4/3/2022, 4/1/2022

1234, Edward, 9/26/2021,

1234, Edward, 1/10/2022, 11/1/2021

1234, Edward, 1/26/2022, 11/1/2021

1234, Edward, 4/20/2021,

];

Output:

You can remove smallest date column if you want.

Hope this helps you.

Regards,

Aditya

[AuroraA]

Yes! Is this what you need?

[Aditya_Chitale]

Hi @AuroraA ,

As per my understanding, you want to compare same ID having multiple term dates and show 'Termination' against each term date having the smallest change date value. 

For this, just change the if() statement (Backend part) from Step 1 of my earlier reply to:

=if(len(trim(TermDate))=0,null(), AutoNumber(TermDate, ID)) as rowno;

Other steps will remain as it is.

Feel free to correct me if I am wrong.

Regards,

Aditya

[PrashantSangle]

Hi, 

I saw, in earlier reply Aditya already provide you good solution. Check his solution, if it won't work for you then can check.

Regards,

Prashant Sangle