Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Rows Extraction

altrx_guy
5 - Atom

I am trying to extract only those rows which are needed. Please read below :-

 

Input

 

IDNameItemTypeCityPlaceValueTS
111XABDLCP15/11/2023 4:45
111XABDLCP15/11/2023 5:45
111XABDLCP05/11/2023 6:45
111XABDLCP05/11/2023 7:45
111XABDLCP05/11/2023 8:45
111XABDLCP05/11/2023 9:45
111XABDLCP15/11/2023 10:45
111XABDLCP15/11/2023 11:45
111XABDLCP15/11/2023 12:45
111XABDLCP15/11/2023 13:45
111XABDLCP05/11/2023 14:45
111XABDLCP05/11/2023 15:45
111XABDLCP05/11/2023 16:45
222XABDLCP15/11/2023 17:45
222XABDLCP15/11/2023 18:45
222XABDLCP15/11/2023 19:45
222XABDLCP15/11/2023 20:45
222XABDLCP15/11/2023 21:45
222XABDLCP15/11/2023 22:45
333XABDLCP05/11/2023 23:45
333XABDLCP05/12/2023 0:45
333XABDLCP05/12/2023 1:45
333XABDLCP15/12/2023 2:45

 

Output - 

 

IDNameItemTypeCityPlaceValueTS
111XABDLCP15/11/2023 5:45
111XABDLCP05/11/2023 6:45
111XABDLCP15/11/2023 10:45
111XABDLCP05/11/2023 14:45
222XABDLCP15/11/2023 22:45
333XABDLCP15/12/2023 2:45

 

The Idea is, we have to fetch only those rows where the Value has this kind of pattern (1, 0, 1), (1, 0, 0, 1), (1, 0, 0, 0, 0, 0, 1). It basicaly means it can have many 0's between 2 1's. Or it can have only patterns as (0, 0, 0, 0, 0) or (1, 1, 1, 1, 1). The GroupBy should be on the columns (ID, Name, Item, Type, City and Place). Also make sure from all the Groups, we must fetch the row with Max TimeStamp (TS) irrespective of what the pattern says. If there are multiple 0's between 2 1's, we just want to fetch the row with Value 0 which is next to Value 1 and discard remaining 0's. Similarly, if we have multiple 1's disregard which are of no need. (Only take rows when it's a part of a pattern or when the TimeStamp is maximum).

 

Please respond!!! Thanks!!!

1 REPLY 1
Clifford_Coon
11 - Bolide

Hi @altrx_guy ,

 

You can use a multi-row formula:

Rows_1.jpg

Rows_2.jpg

Labels