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

Need to organize noin delimited no header flat txt file

rvandel3
8 - Asteroid

Hello I'm very new to Alteryx. and need a little guaidance

I'm trying to make sense of the data within a txt file, its kind of unorganized. Trying to organize our Alteryx server performance data

- 
Controller maskedservername000000 
		16% avg, cpu1 25%, cpu2 15%, cpu3 12%, cpu4 13%	289:40879
	11:41:02 07/08/2020	good	23288GDIAAlteryx	23288GDIA-
- 
Controller maskedservername000000 
		23% virtual 
used, 56194MB virtual 
free, 3.44668 pages/sec	288:40880	23	56194	3.4466820905751034	23	56194	n/a	n/a
	11:40:58 07/08/2020	good	23288GDIAAlteryx	23288GDIA-
- 
Worker maskedservername000000 
		1% avg, cpu1 0%, cpu2 0%, cpu3 1%, cpu4 0%, cpu5 1%, cpu6 1%, cpu7 0%, cpu8 5%, cpu9 3%, cpu10 16%, cpu11 0%, cpu12 0%, cpu13 0%, cpu14 0%, cpu15 0%, cpu16 0%	284:40892
	11:40:57 07/08/2020	good	23288GDIAAlteryx	23288GDIA-

I was thinking of using multirow formula if active row contains "worker" concate the line below,but i cant get it right. thing is its wants to use fields names, there is only one field name since its a flat unstructured file , if it had column names for everything this would be a little easier

I want to concat rows that startwith either "n% avg" or "n% virtual" rows with row startswith controller* or worker* also concat row  "xx:xx:xx dd/dd/dddd"

Can someone point me in the right direction for what I would want to use? There are so many options within Alteryx, it be quicker to ask for the best method to accomplish this?

Hopefully not tall of an order
Thanks all,

15 REPLIES 15
grazitti_sapna
17 - Castor

Hi @rvandel3,

 

It would be great if you could share the text file having unstructured data and the desired output. Meanwhile you can through the link given below and see if it helps:

 

https://community.alteryx.com/t5/Alteryx-Designer/Parsing-Unstructured-Data-from-rows-and-columns-in...

Sapna Gupta
rvandel3
8 - Asteroid

Hi @grazitti_sapna  and thank you for the response,

 

         The code snippit above is what the text file looks like. It is about 120,000 lines long , so far i see that each iteration is the same through out the file.

I will check the link in the meantime, as you can see some servers have more cpu's than others so fixed width wont really work

anyway those are my thoughts i will look into your link after my morning meetings

 

Thank you,

wwatson
12 - Quasar

 I tried it this way. You will need to replace the file path in the input tool to read your file. Good luck.

 

wwatson_0-1594384633463.png

 

wwatson
12 - Quasar

Using a summarise tool may be closer to what you want

wwatson_0-1594385371027.png

 

rvandel3
8 - Asteroid

May I ask @wwatson

Which version of Alteryx are you using?

 

Thanks,

rvandel3
8 - Asteroid

@grazitti_sapna 

2 Mulit-Row Formula:, I did not know you could do this but it is in consideration !

Let me see what I can make with your and @wwatson examples over the weekend.

 

Thank you,

 

wwatson
12 - Quasar

I'm using 2020.2 

rvandel3
8 - Asteroid

So I'm trying to figure the logic here, I'm going to watch a few multi row videos to better understand but from this multi row it goes to the filter and everything is true, because it labels every row as block. I'm trying to wrap my head around
if [Field_1]="-" then [Row-1:block]
elseif [Row-1:Field_1]="-" then [Row-1:block]+1
else [Row-1:block] endif

i can understand if [Field_1]="-" then [Row-1:block] If you come across a - then name that row block so the filter can exclude it ? Or maybe include it ? lol either way dont matter cause the - is not needed

but the else if and the else , im thinking , this is whats throwing off the logic 

I asked about version because as i imported your workflow it said this was made with a different version AND im getting errors such as 

Data Cleansing (25) There is no Question corresponding to the specified App/Macro value "Check Box (136)". The App/Macro may be out of date.

Don't know if there is a big difference between my version (2019.4) and yours 

wwatson
12 - Quasar

You may be getting the error because the data cleanse tool has been updated since 2019.4 and includes extra functionality in 2020.2

if [Field_1]="-" then [Row-1:block]
elseif [Row-1:Field_1]="-" then [Row-1:block]+1
else [Row-1:block] endif

The multi-row formula is creating the values in the block column.

if we look at record 1 Field_1 = "-" so if [Field_1]="-" then [Row-1:block]  set the block column to 0 as that is the default for row-1 for the first row.

Now row 2 Field1 in row 1 ="-" so elseif [Row-1:Field_1]="-" then [Row-1:block]+1 sets block to row 1 block +1 (0+1=1)

Now row 3 Field1 in row 1 !="-" so else [Row-1:block] sets block to the row 2 block value (1)

Now row 4 Field1 in row 1 !="-" so else [Row-1:block] sets block to the row 3 block value (1)

 

Now row 5 Field_1 = "-" so if [Field_1]="-" then [Row-1:block]  sets block to the row 4 block value (1)

Now row 6 Field1 in row 1 ="-" so elseif [Row-1:Field_1]="-" then [Row-1:block]+1 sets block to row 5 block +1 (1+1=2)

 

wwatson_0-1594664041168.png

 

We later filter out the rows where Field-1 = "-" to leave just the blocks we created with the multi-row formula, then concatenate using the summarise tool.

Labels