Add/Delete Month Over Month Comparision

[Sahi]

Hello Team,

Could you please help me with one of the logic am trying to create.

I have a table with Id's for each month.

Id	Month-Year
1	Jan
2	Jan
3	Jan

Id	Month-Year
1.1	Feb
2	Feb
3.1	Feb

Id	Month-Year
1.1	March
2.2	March
3.1	March

Of the 3 months data above, I am seeing if I can get some logic help created below for Addition/Deletion of Id's to get done in Script may be like a flag (Add/Delete) so that I can use it accordingly in my front end. 

Expected output as below:

• • Comparing March vs Feb :

  • Addition(March)	Deletion(Feb)
    2.2	2 since not in March

  • Comparing March vs Jan:

  • Addition(March)	Deletion(Jan)
    1.1	1 since not in March
    2.2	2 since not in March
    3.1	3 since not in March

  • Comparing Feb vs Jan:

  • Addition(Feb)	Deletion(Jan)
    1.1	1 since not in Feb
    3.1	3 since not in Feb

Any help would be more appreciated.

[Marijn]

@Sahi I'm not sure what exactly you are trying to do, but the script below is a way to add flags whether an ID is present in a specific month. These flags can then be used in a set expression.

Jan:
load * inline [
Id,Month-Year
1,Jan
2,Jan
3,Jan 
];

Feb:
noconcatenate load * inline [
Id,Month-Year
1.1,Feb
2,Feb
3.1,Feb
];

Mar:
noconcatenate load * inline [
Id,Month-Year
1.1,March
2.2,March
3.1,March
];

map_Jan:
mapping load Id,1
resident Jan;

map_Feb:
mapping load Id,1
resident Feb;

map_Mar:
mapping load Id,1
resident Mar;

load_all_IDs:
load Id
resident Jan;
concatenate load Id
resident Feb;
concatenate load Id
resident Mar;

unique_IDs_with_flags:
load distinct Id,
ApplyMap('map_Jan',Id,0)	as FL_in_Jan,
ApplyMap('map_Feb',Id,0)	as FL_in_Feb,
ApplyMap('map_Mar',Id,0)	as FL_in_Mar
resident load_all_IDs;

drop tables Jan,Feb,Mar,load_all_IDs;

[Sahi]

Hi Marijn

Thanks for taking time and looking into this.

What I am trying is to compare month over month and find how many Id's are added and got deleted for each month.

. April vs March :

 How many new records are Added in April (means they are not in March but in April so Added)

 How many of records in March which are not in April so they are considered as Deleted (means they are in March but not in April so Deleted)

As per your logic I see that we need to create the tables for each month, I hope that will not help as we get data each month as we move into future.

Thanks,

Sahi

[Marijn]

So all the data comes in one table? Like this:

Id	Month-Year
1	Jan
2	Jan
3	Jan
1.1	Feb
2	Feb
3.1	Feb
1.1	March
2.2	March
3.1	March

I think it would be easier to add flags to your months to create flexibility in your analysis and then use set analysis to make the selections. You can add flags for example for current and last month. It would be easiest to create a calendar dimension table for this.
Flags we use a lot are:
FL_CurrentYear
FL_LastYear
FL_CurrentMonth
FL_LastMonth
FL_BeforeCurrentMonth
FL_BeforeCurrentYear
etc.

Then you can select all ID's that are present in this month but not in last month, for example:

only( {$<FL_CurrentMonth = {1}> - <FL_LastMonth={1}>} Id)

Or ID's that are present in current month, but not in any other month:
only( {$<FL_CurrentMonth={1}> - <FL_BeforeCurrentMonth-={1}>} Id)

See this thread on help how to make a calendar table.