Need help using array data on a dashboard

[madakkas]

Below is the raw data that we have and enters Splunk
Raw Data and each of the application holidays enters as a single record for each date for each application with a unique source type

Raw Data

App 1 Holidays  App 2 Holidays  App 3 Holidays  App 4 Holidays  App 5 Holidays
Date1   Date1   Date1   Date1   Date1
Date2   Date2   Date3   Date2   Date2
Date4   Date3   Date4   Date4   Date4
Date5   Date4   Date5   Date5   Date5
Date6   Date5   Date6   Date6   Date6
Date7   Date6   Date7   Date7   Date7
Date8   Date7   Date8   Date8   Date8
Date9   Date8   Date9   Date9   Date9
Date10  Date9   Date10  Date10  Date10
Date11  Date10  Date11  Date11  Date11
    Date11  

Am trying to build a dashboard wherein it compares all the holidays and gets a list of matched and unmatched holidays. Expectations as below
Part I -
Matched Holidays

Holidays    Application 1   Application 2   Application 3   Application 4   Application 5
Date1   Matched Matched Matched Matched Matched
Date4   Matched Matched Matched Matched Matched
Date5   Matched Matched Matched Matched Matched
Date6   Matched Matched Matched Matched Matched
Date7   Matched Matched Matched Matched Matched
Date8   Matched Matched Matched Matched Matched
Date9   Matched Matched Matched Matched Matched
Date10  Matched Matched Matched Matched Matched
Date11  Matched Matched Matched Matched Matched

Part II

Un Matched Holidays
Holidays    Application 1   Application 2   Application 3   Application 4   Application 5
Date2   Matched Matched No Match    Matched Matched
Date3   No Match    Matched Matched No Match    No Match

I was able to do the needed to create the report for matching holidays(Part I ) but was unable to manage the unmatched holidays.

MFSOURCETYPE=*_HOLIDAY  |replace "DATA" with "" in DATA as DATA | table DATA MFSOURCETYPE | stats COUNT as COUNT by DATA | table DATA COUNT |
eval app1 = if(COUNT = "3" ,"Match", "No Match") |  eval app2 = if(COUNT = "3" ,"Match", "No Match") | eval app3 = if(COUNT = "3" ,"Match", "No Match") |
table DATA app1 app2 app3 | where app1 = "Match"

Sample JSON Data as below

{ [-] 

DATA: DATE1 ,
MFSOURCETYPE: App1_HOLIDAY

}
{ [-] 
   DATA: DATE2 , 
   MFSOURCETYPE: App1_HOLIDAY 
}
{ [-] 
   DATA: DATE1, 
   MFSOURCETYPE: APP2_HOLIDAY 
}   

NOTE – Date Format – “DD-MM-YYYY”

[somesoni2 · Accepted solution]

Give this a try
Part I - Matched Holidays

 MFSOURCETYPE=*_HOLIDAY  |replace "DATA" with "" in DATA as DATA 
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE 
| chart count over DATA by MFSOURCETYPE 
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y" 
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="Y" | fields - keep

Part II - Un Matched Holidays

 MFSOURCETYPE=*_HOLIDAY  |replace "DATA" with "" in DATA as DATA 
| table DATA MFSOURCETYPE | dedup DATA MFSOURCETYPE 
| chart count over DATA by MFSOURCETYPE 
| foreach * [eval "<<FIELD>>"=case('<<FIELD>>'=0,"No Match", '<<FIELD>>'=1,"Matched",true(),'<<FIELD>>') ]
| eval keep="Y" 
| foreach * [eval keep=if('<<FIELD>>'="No Match","N",keep)]
| where keep="N" | fields - keep

[madakkas]

Just to the point and very perfect. Many Thanks

[madakkas]

Would like to add a small feature, but sorry to bug you.

Instead of DATA COLUMN in the table , can we have the report to look like something below which will just highlight the holidays in the application calendar

App 1 Holidays App 2 Holidays App 3 Holidays App 4 Holidays App 5 Holidays
Date1 Date1 Date1 Date1 Date1
Date2 Date2 Date2 Date2
Date3 Date3

Date4 Date4 Date4 Date4 Date4
Date5 Date5 Date5 Date5 Date5
Date6 Date6 Date6 Date6 Date6
Date7 Date7 Date7 Date7 Date7
Date8 Date8 Date8 Date8 Date8
Date9 Date9 Date9 Date9 Date9
Date10 Date10 Date10 Date10 Date10
Date11 Date11 Date11 Date11 Date11