Logic for years where there were no events

[SilviyaK]

Hi all,

I will need some help with the following:

I have the following view (screenshot 1) I count amount of events per account name and look at them per year. 

I did the following:

YearFlag:
LOAD
Account_Name
, Count(Event) as EventCount
, Min([Fiscal Year]) as MinYear
, Max([Fiscal Year]) as MaxYear

Resident YearFlag_Temp
Group By
Account_Name;

In order to get the view from screenshot 2. 

All this gets me somewhere, but not where I want. What I need is the following. In the first screenshot, I can see that the selected account had events in 2019, 2022 and 2023. I need to think of a logic which checks if there was a gap (in this case the gap is 2020 and 2021, because they didn't have an event then) and defines the account as something. If the account had an event, then had a gap and then had an event again, I need to tag it as 'Coming Back'. But currently I can only see the min and max year, which doesn't help that much. 

Does anyone have an idea how to accomplish this with the min and max logic I have created already? I am also open to other ideas different than what I started doing. 

Thanks!

[RsQK]

Hi, maybe something like this:

temp_data:
LOAD * INLINE [
Account,EventID,Year
123,1,2019
123,1,2022
123,1,2023
456,2,2020
456,2,2021
456,2,2022
456,2,2023
];

temp_event_gaps:
LOAD
Account,
Year,
COUNT(DISTINCT EventID) as EventCount
RESIDENT temp_data
GROUP BY Account,Year;

LEFT JOIN (temp_event_gaps)
LOAD
Account,
MIN(Year) as MinYear,
MAX(Year) as MaxYear
RESIDENT temp_data
GROUP BY Account;

temp_event_periods:
LOAD DISTINCT
Account,
MinYear,
MaxYear
RESIDENT temp_event_gaps;

temp_event_periods2:
LOAD
Account,
MinYear + ITERNO() - 1 AS Year
RESIDENT temp_event_periods
WHILE MinYear + ITERNO() - 1 <= MaxYear;

DROP TABLE temp_event_periods;

LEFT JOIN (temp_event_periods2)
LOAD * RESIDENT temp_event_gaps;

DROP TABLE temp_event_gaps;

temp_event_periods3:
LOAD
Account,
IF(LEN(TRIM(EventCount))=0,1,0) AS NullEventFlag
RESIDENT temp_event_periods2;

DROP TABLE temp_event_periods2;

LEFT JOIN (temp_data)
LOAD
Account,
PICK(MATCH(NullEventFlagSum,0)+1,'Coming Back','Ongoing') as EventType;

LOAD
Account,
SUM(NullEventFlag) AS NullEventFlagSum
RESIDENT temp_event_periods3
GROUP BY Account;

DROP TABLE temp_event_periods3;

[SilviyaK]

Hi,

Thanks a lot for the script. I tested it and with the inline table you have it works perfectly. With my data though - it doesn't. It shows everything I have as 'Ongoing'. I was trying to understand what the issue might be and the only thing I can see that is different between your table and my table is that 1 account might have more than one event under their name. 

Any ideas why might be showing everything as 'Ongoing'? It seems that the script only takes into account the places where the event count is above 0 on my side and tags everything as 'Ongoing'. 

[RsQK · Accepted solution]

Ok, so something like this? If thats not the case, send a few sample records.

temp_data:
LOAD * INLINE [
Account,EventID,Year
123,1,2019
123,1,2022
123,1,2023
123,3,2019
123,3,2020
123,3,2021
123,3,2022
456,2,2020
456,2,2021
456,2,2022
456,2,2023
456,4,2018
456,4,2022
];

temp_event_gaps:
LOAD
Account,
Year,
EventID,
COUNT(DISTINCT EventID) as EventCount
RESIDENT temp_data
GROUP BY Account,Year,EventID;

LEFT JOIN (temp_event_gaps)
LOAD
Account,
EventID,
MIN(Year) as MinYear,
MAX(Year) as MaxYear
RESIDENT temp_data
GROUP BY Account,EventID;

temp_event_periods:
LOAD DISTINCT
Account,
EventID,
MinYear,
MaxYear
RESIDENT temp_event_gaps;

temp_event_periods2:
NOCONCATENATE
LOAD
Account,
EventID,
MinYear + ITERNO() - 1 AS Year
RESIDENT temp_event_periods
WHILE MinYear + ITERNO() - 1 <= MaxYear;

DROP TABLE temp_event_periods;

LEFT JOIN (temp_event_periods2)
LOAD * RESIDENT temp_event_gaps;

DROP TABLE temp_event_gaps;

temp_event_periods3:
LOAD
Account,
EventID,
IF(LEN(TRIM(EventCount))=0,1,0) AS NullEventFlag
RESIDENT temp_event_periods2;

DROP TABLE temp_event_periods2;

LEFT JOIN (temp_data)
LOAD
Account,
EventID,
PICK(MATCH(NullEventFlagSum,0)+1,'Coming Back','Ongoing') as EventType;

LOAD
Account,
EventID,
SUM(NullEventFlag) AS NullEventFlagSum
RESIDENT temp_event_periods3
GROUP BY Account,EventID;

DROP TABLE temp_event_periods3;

[SilviyaK]

Hi, 

Thanks for continuing to help! 

I am attaching a sample with the results I get. 

Basically for AMC for example, it is correct to be 'Ongoing', because they had three events in 2019, then they had two events in 2022 and then again two events in 2023. This means that they were 'Coming Back' in 2022 because of the gap of two years, but since they are continuing in 2023, they are indeed 'Ongoing'.

For LV it is the same as AMC.

For Knights though, it is not 'Ongoing', because they just had one event in 2019 and then they had again in 2023 so they should be tagged as 'Coming Back'. 

And finally the ENG is a third case, which should be tagged as 'Lost', not 'Ongoing' because they had only one event in 2019 and since then - nothing. 

Thanks in advance! 

[SilviyaK]

Actually, it was my bad, I just saw I missed something. The code works perfectly, I just need to add a condition for the lost ones. 

Thank you very, very much!