Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Pull top level data in to table from vertical format

Highlighted
7 - Meteor

Hi,

 

I have the attached workflow that uses a macro to look through our job directories find spreadsheets that contain a sheet called Master Budget and then pull the information from it. The problem i'm having is that the gyms have now changed the template to the attached Dummy Budget 1 format.

 

In cell B1 is the Job number that i need to pull in order to run joins off after we have the information. How can i pull that number out with the Prefix TP. they have made it a custom format so that it doesnt recognize the TP no: at the start. see below.

 

I would like to pull the information in to a data table that would also take figure from the Grand total column beginning in column F12. If the table could be outputted as below.

 

The directory will have to run through about 500+ of these sheets but they should be in the exact same format as these.

 

Job NumberProject NameMaterialsExternal Manufactured ItemsOVERSEAS PROCUREMENTPaintProduction PlanningFactorySiteLogistics
TP5555 36401.200000040921.22527.00

 

jdinsmore90_0-1584938407317.png

 

17 - Castor
17 - Castor

Hi @chelseariver90 

 

They did more than just change the format of the TP No.  The entire file is different.  Attached is the original file (Budget Original) and the current one.  The key to the whole process is first multi-row formula that determines the row type, H1, H2, etc.  Here the formula that's used

 

 

 

 

if isempty([Row-1:Row Type]) then
	"H1"
elseif [F5] = "GRAND TOTAL INC. WASTE" then
	"H2"
elseif [F2]="MARK-UP (%)" then
	"H3"
elseif [F5] = "GRAND TOTAL INC. MARK-UP" then
	"H4"
elseif [F1] = "1" then
	"D"
else
	[Row-1:Row Type]
Endif

 

 

 

 

As you can see, It's looking for specific strings in specific columns.  These have changed.  For instance, the first else looks for  "GRAND TOTAL INC. WASTE". This header is gone.  As is the  "Mark-UP (%)" label. "Grand Total Inc. Mark-up" is now "Grand Total"

 

Before you had 4 header sections before the data started.  Now you have only 3. 

 

h.png

 

Modify the multi-row formula to find these 3 sections using the corresponding start header text. 

 

For the formatting problem, I've added a Multi-row  in the Job... container that adds TP NO: if the row after it contains "Quote No:".  

 

n.png

 

Dan

 

Labels