Convert 2 fields to one

[LincolnMike]

Hello,

I need your assistance.

My source data has 4 fields of data:

VIN	MY	Veh	Ques	Ans
123	17	x	soc1a	25
123	17	x	soc2	75
456	18	b	soc1b	35
456	18	b	soc2	85

I want SOC1a and SOC1b to be just one field (for all future answers SOC1b  replaced SOC1a, but I still require all the SOC1a for analytics).  

My output should be:

VIN	MY	Veh	SOC1	SOC2	DIFF
123	17	x	25	75	50
456	18	b	35	85	50

But I am stuck with this:

VIN	MY	Veh	SOC1a	SOC1b	SOC2	Diff1	Diff2
123	17	x	25		75	50
456	18	b		35	85		50

I am using Alteryx 10.6, and I attached a mockup.

thank you.

[sjm · Accepted solution]

Would it help to change your formula tool to only have the following calcs?

- Create a soc1_combined field

IF IsEmpty([soc1a]) THEN [soc1b] ELSE [soc1a]ENDIF

- Keep one difference calc

[soc2]-[soc1_combined]

- Remove the original soc1a and soc1b columns if needed (not totally clear to me if you need them or not)

[jrgo · Accepted solution]

@LincolnMike,

---

@LincolnMike wrote:

I want SOC1a and SOC1b to be just one field (for all future answers SOC1b  replaced SOC1a, but I still require all the SOC1a for analytics).

---

I'm not sure what you're referring to on the above because your desired output didn't seem to 'replace' anything else.

However, the updated workflow attached does generate an output to match what you're looking for. Note that I use REGEX to prep the value in "Ques" to remove any characters after the string pattern of SOC followed by 1 or more digits. If ALL your questions do not match that pattern, it may not operate properly. That said, this should give you some ideas on how to approach your solution.

[LincolnMike]

thank you.   This worked.  That is so simple to use the IsEmpty.

I confused the issue...soc1a and soc1b just needed combining, and calling it SOC1combined is fine.

[LincolnMike]

thank you jrgo.

I confused the issue...soc1a and soc1b just needed combining, and calling it SOC1 is fine.

and thanks for the REGEX example and explanation.  I believe I have another use for that in other queries.