count two fields by their (not necessarily common) values

[petreb]

Hello,

I have the following table

item	attribute	first_seen	last_seen
item1	attr1	01.01.1970	01.01.2000
item2	attr2	01.01.1970	01.01.2020
item3	attr3	01.01.2021	01.01.2021

and I would like to count the last two fields based on their value (as in the table below). 

date	count(first_seen)	count(last_seen)
01.01.1970	2	0
01.01.2000	0	1
01.01.2020	0	1
01.01.2021	1	1

I have tried to achieve this by using two searches and appending them, but I'm stuck at grouping them by date, which is a value of the 2 fields which can be common or not.

..........
| stats count by last_seen
| append [
..........
| stats count by first_seen]

gives me the table below, which is a step forward, but far from what I need

last_seen	count	first_seen
01.01.1970	0
01.01.2000	1
01.01.2020	1
01.01.2021	1
	2	01.01.1970
	0	01.01.2000
	0	01.01.2020
	1	01.01.2021

can anyone give me a hint?

cheers 

[scelikok]

Hi @petreb,

Please try below;

..........
| stats count as count_last by last_seen
| append [
..........
| stats count as count_first by first_seen]
| rename last_seen as date, first_seen as date
| stats list(count_first) as count(last_seen) list(count_last) as count(first_seen) by date

[petreb]

isn't what I needed (the last column is still empty), but gave me other ideas which kind of solved my problem 🙂