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)

[mceleavey]

Hi @shreya2811 ,

Can you post some mock data so we can help?

M.

[cplewis90]

Without seeing how exactly the data is set up, whenever I want to compare rows of data, I tend to use the Multi-Row formula tool. The biggest thing about this tool is sorting your data correctly. In this instance if you sort based on the sequence number you have and then the week end date you would be able to then use the Multi-Row formula tool to see if the sequence number occurs in the previous week in an if then else statement. Once you post some mock data I could build this out in a sample.

Best,

Chris Lewis

[shreya2811]

Sure, here is some quick sample data I created for better understanding. I have worth 52 weeks of data so I internally need to compare each week with previous week and update the sale type column as below 

Name	Amount	Account id	Seq	Acc_seq	Week	Sale Type
mike	2000	1234	1	12341	Mar-12	new
mike	2000	2122	1	21221	Mar-12	new
mike	2000	2123	1	21231	Mar-12	not new
mike	2000	2123	2	21232	Mar-12	not new
mike	2000	2100	1	21001	Mar-12	not new
mike	2000	2500	1	25001	Mar-12	not new
mike	2000	2501	1	25011	Mar-12	not new
mike	2000	2123	1	21231	Mar-05	-
mike	2000	2123	2	21232	Mar-05	-
mike	2000	2100	1	21001	Mar-05	-
mike	2000	2500	1	25001	Mar-05	-
mike	2000	2501	1	25011	Mar-05	-

[shreya2811]

Hi @mceleavey . Please see the sample data posted below. Thanks

[mceleavey]

Hi @shreya2811 ,

apologies for the delay, I'm distracted by work at the moment!

Can you confirm what you're looking for, because what you've described does not tie up with the data example. You say you want to find where a sequence appeared in a the previous week, but I can see in the data that both sequences (1 and 2) in week Mar-12 appeared in week Mar-05, and yet two are marked as new. Do you mean Acc-seq or a combination of the two?

M.

[cplewis90]

Hello @shreya2811,

Here is my solution. I first adjusted the date into a date format Alteryx recognizes, sorted the data and then created the calculation.

I also attached the workflow for your viewing.

[shreya2811]

Sorry for the confusion, I meant the Acc_seq column, if that appears in previous week, then I need to mark it as new. Similarly, I will compare Mar-5 records with Feb-26 records to fill up the Sale Type column, hope that clarifies your question. 

[shreya2811]

Thanks Lewis, I think am almost there. The difference is that in reality, I have no minimum date but just all weeks within a year. So every week's set of records need to be compared with previous week's set of records. Do you think it will help to add a "Previous week" column instead? How do I compare with just previous week's set of records? 

[mceleavey]

Hi @shreya2811 , no problem.

@cplewis90  has a less complicated solution, but I've attached my macro solution which loops through every week and assigns it. You might need to change the order and sequence of the weeks, but it should work.

M