Nested query within dataset

[shreya2811]

Hi All, 

The input tool reads each weekly xls files and combines it one below the other to create one combined data file. My data set has weekly records with a column "week Ending" mentioning end of week date. I want to compare each week's "Seq" column against all sequences in previous week(CW-1) to see if it exists in previous week or not. Accordingly, a new column "Saletype" will get created with "New" as value if it does not exist in previous week else "Not new" if it exists in previous week. I can do this in sql with a query like this below but need guidance on how to achieve it in Alteryx: 

select
"New" as Sale type 
from data A
where A.accseq in
( select B.accseq from data B where B.date = A.date - 7)

[shreya2811]

Thank you for the prompt response, really appreciate it!

Unfortunately, am working on an older version so it doesn't allow me to open your workflow - I will try both methods as suggested and hopefully I will nail this down by today. 

Thanks again for all your help, hope we cross paths again 🙂