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

import File

Scheruku
8 - Asteroid

Hi All,

 

I'm facing issues with reading a series of data which has multiple lines. Please see below the format...

 

Sheet 1 has actual lines...sheet 2 has output

3 REPLIES 3
Joe_Mako
12 - Quasar

How about something like the attached?

Scheruku
8 - Asteroid

Thank you very much...I'm still learning Alteryx and if you can explain the formulas it would help me

Joe_Mako
12 - Quasar

Import File.png

- Record ID to to keep track of the order of the records, since lots of data needs to be filled up or down.
- Multi-Field Formula to clean up all cells, trim and replace empty string with Null for formulas later to check if a cell is blank or not.
- Mulit-Row Formula to create "ParentID", if both Debit and Credit are blank, use the previous ParentID, if there is a value in either, it's RecordID is used as the ParentID.
- Filter into two streams, one for Parent, one for child (if RecordID=ParentID it is a parent record)
- in child stream, Summarize, grouping by ParentID, and concatenating to create the fields: Narration,Field1,Field2
- Multi-Field Formula to clean up all cells, trim and replace empty string with Null
- in parent stream, three Multi-Row Formula tools to fill down the fields: Date,Vch Type,Vch No.
- Multi-Filed Formula to replace null with zero in the Debit and Credit fields
- Join the streams on RecordID and ParentID
- Union so all record from the parent stream are retained
- Select to arrange the fields
- Sort by RecordID descending
- Multi-Row Formula to fill Narration up
- Sort into final display order

Labels