community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Data Preparation

Hello i'm new to alteryx

been working on qlikView / Qlik Sense since 2011

Now we got a project in which we ought to use Alteryx

I got several questions on transformation scenarios and I want to know how to do them in Alteryx

 

First I got a series of Excel sheets in a specified path, all the excel sheets got the same structure so what I want is to loop through all available excel files read data and concatenate them in one table and make the result available for use in Qlik

 

Second I want to know how to make cross table i.e. suppose I got for each branch the target amount per month, but the months numbers are columns so what I want is transform columns to rows so that for each branch I get 12 rows representing the 12 months of the year

 

Third question suppose I got rows for financial accounts sorted by account number and date as follows:

Account        Date        Amount

A                   D1           325

A                   D2           250

A                   D3           100

 

what I want to accomplish is having a new column called cumulative amount which would contain the following values

Account        Date        Amount        Cumulative Amount

A                   D1           325              325

A                   D2           250              575

A                   D3           100              675

 

Kindly advise on how to accomplish these

The easiest way is to use a Running Total tool

 

It will add a cumulative amount to the data set.

Highlighted

what about the first question regarding parsing several excel or xml files in a folder?

can I run a loop in a workflow?

kindly advise

Pulsar

For your first question, it sounds like you're going to need to use the Directory tool, along with a Dynamic Input and an Iterative Macro.

 

For your second question, look into the Transpose tool.  It allows you to pivot data this way, selecting your month columns as your "Data Fields".

 

For the third, jdunkerley's advise to use the Running Total tool is your best bet.

is it possible to share a sample/example regarding my first question

as I want to loop through all files have them in one table (concatenated) and finally save them in one QVX file for Qlik Sense to consume

Labels