How to keep only first row of a column?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Labels:
- Help
- Tips and Tricks
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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,
- Use Multi-Row formula to create ID field
- 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
