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.
SOLVED

Nedd your help for space seperated data with some different format

alt_tush
9 - Comet

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.

 

8 REPLIES 8
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
9 - Comet

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
9 - Comet

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
ACE Emeritus
ACE Emeritus

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
9 - Comet

Yes Ben. Your are right :)

It works now.

 

Thank you so much for your help :)

alt_tush
9 - Comet

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