First N records

[zuruprem]

Hi ,

I am new to the Alteryx. Challenge for me analysing the Survey responce which resides in CSV files.

I have responces for all 22 questions.  my sample data for question 1 look like below in CSV file


Here I just want to filter only row 1 and send to a file ? Could you please suugest me a way to get only first 1 row (I am new please suggest the right tool)
 

[PaulT · Accepted solution]

Greetings! and welcome to the wonderful wide world of Alteryx!

The tool you're looking for is the Sample tool. This will allow you to select the First N records. The tool is defaulted to N=100, but can be changed to whatever value you want. There are also additonal options in the Sample tool which allow you to:

Take the Last N Records
Skip the 1st N Records
take 1 of every N Records
Random 1 in N chance for each Record
and First N% of records

Hope this helps!

Thanks,

Paul

[anujaymathur]

Hey @PaulT,

Coming back to you after about 1 year of your solution.

I am using the sample tool to pull out first 1000 records from my file.

To take out this sample, the Alteryx still spends time in reading the entire file (>17Mn Records)

Is there a way I can stop the process the minute my first 1000 records are read, and save a lot of time.

Regards,

Anujay

[danielbrun2]

Hi Anujay,

You coult put a limit on the input file. See attached screenshot.

 

Bonus: You can also limit all input tools to a number og rows.

 

Best,

Daniel

[Raghu_s]

Hi @PaulT, 

Am solving a similar  problem where I need to limit overflowing record limits for a group in my data set.

EX:

GRP 1 has 10000 records - where I need only 6500 records

GRP 2 has 12000 records - where I need only 7500 records

GRP 3 has 19000 records. - where I need only 8500 records

Is there a way that we can parse this highlighted set of values for each group to the N field as in the case of sample tool ? Is there any other tool that can be combined to get this neatly? 

For now, I have been manually writing filters to look up every group and then limit the records needed for every group. This gets difficult if I have 20+ groups and its time consuming as well. 

Any thought would be appreciated. 

[PaulT]

Greetings Raghu,

If you already have a list of how many records you want from each group then you will want to investigate the use of a Batch Macro. Essentially, you will use your unique list of groups and associate record counts as your "Control Parameter." Inside the Batch Macro you will use a Filter tool filter out records by group, and the subsequently use a Sample tool to take only the relevant number of records based on the group that was filtered. The Control Parameter list will filter out each group, and pull the related number of records from the sample tool accordingly. The Macro Output will basically Union the results of each of the Batch iterations to yield a table containing only the records you desire.

New to Batch Macros? Check out the training video available here: https://www.alteryx.com/self-paced-training

Cheers,

Paul

[jrgo]

@Raghu_s,

with th the assumption that this is for one data source and that the records within each group is in the correct sort order, use a Multi-Row Formula tool to create a new field (let’s call it GRP_RowNum) which will simply increment the previous records num +1 (e.g. Row-1:[GRP_RowNum] + 1). The important part of this is to be sure that you select the field that identifies the what group the record belongs in within the “Group by” list box. This resets the count back to 1 when it starts on the next GRP.

Next step would be to join in your group record limit. Lastly, use a filter tool to only keep records where [GRP_RowNum] <= [Record Limit]. 

Advantages with this approach is that you won’t clutter your canvas with filters and you only have to read in the data once. Paul’s batch macro would certainly work, but may take longer to complete as the entire table will need to be evaluated for each batch iteration.

Hope this helps!

Best,

Jimmy

[Raghu_s]

hey @jrgo ! 

Bingo, actually after I posted this request here in this thread, did some research again in the community and I came across such a scenario and the usage of Multi row formula.  It made the job simple. I just got to see this response and am happy to see I'm right track of finding out the right set of tools to process such request. 

Thanks for the help! 

Thanks @PaulT. Macros are new to me and am interested to use macros to build a generalized solution for such problems. Will definitely start the macros training coming week. 

Cheers!