subtituting values of raws to columns

[Faisalomran1]

hello everyone .

so I have a problem in designing and im not sure if it is doable by using Alteryx.

 

 in the data sample above each API number ( Second column ) is represented by more than one job start date (first column),the Chemical used ( CAS number, third column), percentage of the chemical used ( percent HFjob, 4th column), and the mass of ingredient used (mass ingredient, fifth column).

what I am trying to do is to make a column for each chemichal used ( CAS number), including only the written one ( not the numbers). the new columns will be; mass of chemical taken from ( mass ingredient ), and percentage of chemical taken from ( percentHFjob).

sorry for the complication.

your help will be much appreciated      

[grossal]

Hi @Faisalomran1,

I am not sure if I got the problem right (small sample data and desired output would be useful), but I will try my best.

"what I am trying to do is to make a column for each chemichal used ( CAS number), including only the written one ( not the numbers"

-> you can do this with a Filter Tool and a Regex-Expression (can be done in one or two steps)

-> make a column afterwards -> Cross Tab

Hope this helps a bit. 

Best

Alex

[Faisalomran1]

input:

job start date	API number	CAS number	percent HFjob	massingredient
2018-01-09	1107200500001	water	3	33
2018-01-09	1107200500001	water	57	3358
2018-01-09	1107200500001	proppant	30	6520
2018-01-26	1107203940000	gelling agent	1	20
2018-01-26	1107203940000	acid	1	30
2018-01-26	1107203940000	water	40	3256
2017-02-14	1107204000000	64742-47-8	1	21
2017-02-14	1107204000000	proppant	20	25
2017-02-14	1107204000000	64742-47-6	1	20

desired output

job start date	API number	water mass	proppant mass	acid mass	gelling agent mass	proppant precentage	water precentage	acid precentage
2018-01-09	1107200500001	3358	6520	0	0	30	60 (57+3)	0
2018-01-26	1107203940000	3256	0	30	20	0	40	1
2017-02-14	1107204000000	0	25	0	0	20	0	0

[grossal · Accepted solution]

Thanks for the sample @Faisalomran1.

Here is a way to do it: 

What happens:

1) Filter all CAS numbers with Text

2) Rename Columns to percentage / mass (needed for the output) (Select Tool)

3) Transpose to shift columns to rows

4) Formula for the required column names

5) Cross Tab to bring them back into the wanted shape

6) Multi Field Formula to clean up null values and replace them with 0

If the column order matters to you, you can use a select tool and change them. Workflow is attached. I hope I got it right.

Best 

Alex