Alteryx Designer Discussions

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

General Discussions has some can't miss conversations going on right now! From conversations about automation to sharing your favorite Alteryx memes, there's something for everyone. Make it part of your community routine!

SOLVED

Nedd your help for space seperated data with some different format

alt_tush
8 - Asteroid

Hi Team,

 

I have a space separated data in text file.

Format of the data are below :-

 

ABC20180322 00000MAR 0000000000000000000000000000000000000000000000000000000000

1 ABC 10 HR

2 DEF 20 ADMIN

3 PQR 30 IT

4 XYZ 40 HELPDESK

ABC20180322 00000MAR 0000000000000000000000000000000000000000000000000000000000

 

I want all the 4 records in excel in between first and last row.

First and last row should be ignore. Also there would be "N" number of records in between first and last row.

 

Please need your assistance here.

 

Please help

 

Thank you in advance.

 

LordNeilLord
15 - Aurora

Hey @alt_tush

 

If you're data is always going to be in that consistent format, I'd use regex to identify a pattern...for example the first column always contains a recordid...then you can filter out the fields you don't need.

 

RegExFilter.PNG@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

alt_tush
8 - Asteroid

Hi Neil

 

Thanks for solution but my all fields are alphanumeric.

 

There is no any field is numeric value. In such case how can I handle this situation?

 

Once again thank you.

LordNeilLord
15 - Aurora

Hey @alt_tush

 

Does the header always have those 00000000000000000000000 in the header?

 

An actual example of the headers would be useful

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

alt_tush
8 - Asteroid

Yes header would be always same. But data is always alphanumeric in all the fields.

 

Thank you.

LordNeilLord
15 - Aurora

But if it always has a consistent format or pattern you can search for that...for example if the header always has 10 number in it you can use RegEx_Match(Field1, '\d{10}') and it will find anything that matches that

 

 

BenMoss
17 - Castor
17 - Castor

Would a simple solution not be to use the sample tool.

First of all recordID your data.

Use the sample tool to 'skip the 1st n(1)' record.

Then sort your data on the recordID descending.

Use the sample tool again to 'skip the 1st n(1) record.

Resort your data back into the original order (ascending).

Ben

alt_tush
8 - Asteroid

Yes Ben. Your are right 🙂

It works now.

 

Thank you so much for your help 🙂

alt_tush
8 - Asteroid

Hi Neil,

 

Thank you for your help.

 

I got the solution as Ben shared with us. He suggest me

 

First of all recordID your data.

Use the sample tool to 'skip the 1st n(1)' record.

Then sort your data on the recordID descending.

Use the sample tool again to 'skip the 1st n(1) record.

Resort your data back into the original order (ascending).

 

Thank you so much you help me always 🙂

Labels