How to keep only first row of a column?

[Aviator0807]

What I have right now

Month	Year	Sales	Store
April	2021	$$$	0478
April	2021	$$$	0631
April	2021	$$$	0512
April	2021	$$$	0154

What I would like is 

Month	Year	Sales	Store
April	2021	$$$	0478
		$$$	0631
		$$$	0512
		$$$	0154

[mst3k]

a multi row formula tool could do this, the formula for Month for example would be:

if [Month]=[Row-1:Month] then null() else [Month] endif

then repeat that for the Year column. you'll need to make sure your data is sorted the way you want it..

[AngelosPachis]

Hi @Aviator0807 ,

Here's one way that you can do it

You form the date field in the first formula tool and then check if the date repeats itself with the previous row, so you only keep the first occurrence.

Hope that helps,

Angelos

[Aviator0807]

@mst3k I tried that but wasnt getting the result I need.

This is what I was getting when I used that. 

Month	Year	Sales	Store
April	2021	$$$	0487
		$$$	0235
April	2021	$$$	0951
		$$$	0520
April	2021	$$$	0874

[AngelosPachis]

@mst3k suggestion should work if you create new fields with the multi-row formula tools, and then replace the old ones with a select tool

The issue that may arise with this approach though @Aviator0807  is that in case you have the same month but different years stacked on top of each other, that expressions will not address that change in year, as show below

Ideally this is why you should form a date field as proposed in my first post and then probably group by that field in the multi-row formula tool, before creating the logic.

[ncrlelia]

Hi @Aviator0807, 

I would suggest to create an ID for each group of data so that it is easier to follow through.

Assuming that your group is defined by Month and Year,

1. Use Multi-Row formula to create ID field
   • 
2. Use a formula tool to keep current Month and Year value if ID = 1.
   • I've set Year to String so that I can use Multi-Field formula. You can simply write 2 formula using the Formula tool for Month and Year separately, if your Year has to be Numeric 
   • 

Hope this helps.

Cheers,

Lelia