Data manipulation with rows & columns

[Nawaz]

Hi,

Can somebody help on the below query that would be great.

Please find the attached file which has the headers Rows 1, 2 and 3.

In Row1 & 2 there are many null columns only columns has the data is AB, AT & BL. Wherever we find the values can we club that with the below "cells".

which means for example Row1, Column AB has "    V1000 - Total Sales Volume" and below cell has "Current Month (AUG 2016)" and again the below cell has "08.2016", can we concatenate this as "    V1000 - Total Sales Volume Current Month  (AUG 2016) 8.2016". This should happen only for these three rows, since further below there would be data which i have not posted (those should be kept unaffected).

If you can see Column A, Row no 3 has header "Parent" whereas Column B, Row no. 3 doesnt have header can we replicate the earlier value ("Parent" or adding some prefix/suffix here).

How to perform the above two tasks, if some body can guide that woul be good for me.

Thanks & Regards

Nawaz

[pcatterson]

To accomplish this, you'd need to do a whole bunch of Multi-Row Formulas along with some Multi-Field Formulas.  It would probably be easier to just manually assign the names of the fields with a select, but here is a more methodical way to handle.

[Nawaz]

Hi Friend,

Thanks for the solution, couple of things which i was missing in my earlier communication is of the empty columns.

1. Need to copy the earlier header with "description" text as additional, since i want these columns as well since it will hold the data below (no need to exclude the columns).

2. where ever there are 3 records, both the three records should be combined.

For example:- the headers should be V1000 - "Total Sales Volume Current Month (AUG 2016) 08.2016"

                                                                          "Total Sales Volume Previous Month (JUL 2016) 07.2016" 

                                                                          "Total Sales Volume Current YTD (JAN 2016 to AUG 2016) 01.2016" 

Thanks in advance for your hlep.

Regards

Nawaz

[Nawaz]

Hi Friend,

Thanks for the solution, couple of things which i was missing in my earlier communication is of the empty columns.

1. Need to copy the earlier header with "description" text as additional, since i want these columns as well since it will hold the data below (no need to exclude the columns).

2. where ever there are 3 records, both the three records should be combined.

For example:- the headers should be V1000 - "Total Sales Volume Current Month (AUG 2016) 08.2016"

                                                                          "Total Sales Volume Previous Month (JUL 2016) 07.2016" 

                                                                          "Total Sales Volume Current YTD (JAN 2016 to AUG 2016) 01.2016" 

Forgot to mention in my earlier communication this is a YTD report hence month columns would be added as the period goes, need to consider that as well.

Thanks in advance for your hlep.

Regards

Nawaz

[pcatterson]

It can be done in alteryx, as I've shown, but it seems more cumbersome than valuable.  Due to the nature of your current headers, it would probably be easier to clean up the headers outside of alteryx especially if additional months will be added throughout the year.

[Nawaz]

Hi Pcatterson,

Thanks for the valuable suggestions.

If you could suggest how to do it Alteryx that would help in a great manner in my learning curve.

Thanks

Nawaz

[pcatterson · Accepted solution]

Ok, here is a solution that I think will work more generally if your files will have different columns.

[Nawaz]

Thanks a lot for your kind help

[Nawaz]

Hi Pcatterson,

All is looking well have a query need your solution.

Query 1:-

When we are doing dynamic rename for the headers we are missing out a field value called "Parent" because of our multi row formulae

IF [RecordID] != [Row-1:RecordID]
THEN NULL()
ELSEIF [RecordID] < 3 and IsEmpty([Value])
THEN [Row-1:Value]
ELSE [Value]
ENDIF

Record ID 1, NAME 001, Value is displaying [Null] instead of "Parent".

I have tried lot of times to correct this but unable to.

Query 2:-

When one of the new columns in the data part (not in the header those are static) since it is dynamic data (YTD report months would keep on adding).

this column names are keep on changing from Field_82 to some other field names such as Field_89 because of which iam unable to rename the columns which i need. Attached spreadsheet with dummy details highlighted columns in Amber color

Thanks & Regards for your help.

Nawaz

[pcatterson]

I wasn't sure what you meant by your issues/queries, but I gave my best to try and figure out what you meant.

Take a look.