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

How to Update header with above row if Blank

Mukeshy12390
8 - Asteroid

deHi All,

 

I have been trying to build a solution to make the header standard. I get an input file in which I have to some manual adjustments to get the expected header. below are the snap of input and expected output. Please let me know if anyone know how it can be done.

 

Input      MonthJan  Mar  Feb  Apr  Jun  Jul  
FirstNameLastNameAddressCityRegionPostalCodeCountryPhoneamtTaxDateamtTaxDateamtTaxDateamtTaxDateamtTaxDateamtTaxDate
BobAllen130 17th St.VancouverBCV4T 1Y9Canada(604) 555-0192973506-05-2022574906-05-2022897906-05-2022761106-05-202281706-05-2022937606-05-2022
NickAshton89 Cedar WayRedmondWA88052USA(425) 555-0191714206-05-2022147206-05-2022701706-05-2022295106-05-2022967706-05-2022274406-05-2022
JuliaBergPO Box 69YakimaWA88902USA(509) 555-0188392606-05-2022345506-05-2022471706-05-202267806-05-2022801906-05-2022655606-05-2022
JackBoseman55 Grizzly Peak Rd.ButteMT49707USA(406) 555-0186163706-05-2022754906-05-202228706-05-202278706-05-202245706-05-2022371506-05-2022
JohnCampbell22 Market St.San FranciscoCA84112USA(415) 555-0183159706-05-2022919706-05-2022162106-05-2022547706-05-2022576406-05-2022731506-05-2022
SamCannon89 W. Hilltop Dr.Palo AltoCA84306USA(415) 555-0182332306-05-20221002606-05-2022567006-05-202299806-05-2022136306-05-2022787506-05-2022
DeanFunk1815 Yolo St.SeattleWA88117USA(425) 555-0166552206-05-2022228806-05-2022435006-05-2022987106-05-2022216406-05-2022724706-05-2022
                          
                          
                          
                          
                          
                          
                          
Output      MonthJanJanJanMarMarMarFebFebFebAprAprAprJunJunJunJulJulJul
FirstNameLastNameAddressCityRegionPostalCodeCountryPhoneamt_JanTax_JanDate_Janamt_MarTax_MarDate_Maramt_FebTax_FebDate_Febamt_AprTax_AprDate_Apramt_JunTax_JunDate_Junamt_JulTax_JulDate_Jul
BobAllen130 17th St.VancouverBCV4T 1Y9Canada(604) 555-0192973506-05-2022574906-05-2022897906-05-2022761106-05-202281706-05-2022937606-05-2022
NickAshton89 Cedar WayRedmondWA88052USA(425) 555-0191714206-05-2022147206-05-2022701706-05-2022295106-05-2022967706-05-2022274406-05-2022
JuliaBergPO Box 69YakimaWA88902USA(509) 555-0188392606-05-2022345506-05-2022471706-05-202267806-05-2022801906-05-2022655606-05-2022
JackBoseman55 Grizzly Peak Rd.ButteMT49707USA(406) 555-0186163706-05-2022754906-05-202228706-05-202278706-05-202245706-05-2022371506-05-2022
JohnCampbell22 Market St.San FranciscoCA84112USA(415) 555-0183159706-05-2022919706-05-2022162106-05-2022547706-05-2022576406-05-2022731506-05-2022
SamCannon89 W. Hilltop Dr.Palo AltoCA84306USA(415) 555-0182332306-05-20221002606-05-2022567006-05-202299806-05-2022136306-05-2022787506-05-2022
DeanFunk1815 Yolo St.SeattleWA88117USA(425) 555-0166552206-05-2022228806-05-2022435006-05-2022987106-05-2022216406-05-2022724706-05-2022

 

 

.

 

 

4 REPLIES 4
DawnDuong
13 - Pulsar
13 - Pulsar

Hi @Mukeshy12390 

this can be done by a combination of Transpose, Summary and RecordID tool.

the rough schematics is:

1) read input specifying that row 1 contains data. This way allows you to standardise the column names later on.

2) using the “Select Record” toolafter the Input in step 1 to select any rows whose combination you want to use as headers later. In this case, the 2nd and 3rd rows are the header rows. Then you Tranpose all columns, which will give you 3 columns:

- 1 is the colNumber eg F1 F2 etc…

- 2 columns contains the data for the unpivoted headerRows. > concatenate them with your preferred delimiter for example “_” > then you have the new complex header row.

3) using the Select tool after the Input in step 1 to select the data rows. In this case, you should select 4+ to select all the rows from the 4th row onwards. 

With this you add a RecordID

Then use a Transpose tool to unpivot all colums, except the recordId. Then it will give you:

- 1 is the ColNumber F1 F2 etc…

- one Name column

- 1 value column

4) join the outputs from 2 and 3 by the colNumber (F1, F2 values). This is a crucial step that allows you to map the “complex header” back to the data using the F1 F2 etc. reference.

at this stage it should be relatively straightforward for you to use the Summarize tool and others to get the output data back, so i don’t elaborate here.

cheers,
dawn 

 

Mukeshy12390
8 - Asteroid

thanks for the solution.

 

I have tried but couldn't get the whole step...could you share an example pls?

 

 

 

binuacs
20 - Arcturus

@Mukeshy12390 one way of doing this

 

binuacs_1-1651908311791.png

 

binuacs_2-1651908344392.png

 

 

flying008
14 - Magnetar

Hi, @Mukeshy12390 

 

Dear, there is 2 solutions for you. 

 

1- Common workflow, maybe have complex steps.

 

录制_2022_05_08_09_51_45_497.gif

 

2- use macro like crew, but this way is so easy ! 

 

录制_2022_05_08_09_53_53_434.gif

Labels