Sample tool - inconsistent data

qgpl

Hello

I'm trying to transform data that looks like this:

COMPANY NAME

COMPANY ACC

AVAILABLE BALANCE

****.**

BALANCE

****.**

COMPANY NAME

COMPANY ACC

BALANCE

****.**

The second part of the data I have doesn't have the "available balance" in. I've looked through here and see what I can do and came across the Sample tool but not sure how I can use it if my N number isn't fixed? I've also tried to use transpose and crosstabbing but no luck there either. Is there another tool I can have a look at?

Thanks in advance

Workflow

Datasets

Designer Cloud

Custom Tools

Help

Accepted answers

All comments

ChrisTX

Can you provide expected output? Does your sample input provide an accurate view of what your full/actual data looks like?

For what looks like the second record:

Do you want BALANCE to be in the same column as AVAILABLE BALANCE?

Would you expect the other two fields to be blank in the output?

Chris

qgpl

Hi Chris

Sorry I should have stated that, my expected output needs to look like:

My expected output is:

COMPANY NAME	COMPANY ACC	BALANCE	CCY
COMPANY 1	12345	**.	EUR
COMPANY 2	678910	**.	GBP

I have attached a more accurate sample.

I need the BALANCE in the output and the AVAILABLE BALANCE can be blank.

Thank you

SAMPLE DATA.xlsx

qgpl

@ChrisTX - sorry I forgot to click on Reply

ChrisTX

If the Balance is "****.**", do you need to retain that in the output or can the value be blank?

Some of the Balance values are numbers like 1138125.89

If you want to include the Balance "****.**" values, the field will need to be a String data type, and you won't be able add up the values in the field.

Chris

qgpl

It can be left blank

Thank you

ChrisTX

Using the data from your XLSX file, it looks like all values for COMPANY NAME and COMPANY ACC will be blank. Correct?

Sample input data that is truly representative of your actual data is important. In cases like this, we are basically adding tools to fit the specific data layout.

Chris

qgpl

Thanks Chris.

COMPANY NAME will be filled in with all different entities/companies and COMPANY ACC will be a mix of numbers and letters as this is an IBAN account.

I've updated the sample data. This is the exact format -- I have just removed and edited the company names, amounts and the IBAN numbers.

Thanks

Quennie

ChrisTX

The attached workflow should point you in the right direction.

Chris

Sample tool - inconsistent data.yxmd

qgpl

Hi Chris

Thanks so much for this workflow.

Our replies must have crossed. I did try to use and play around with your workflow though however I'm still learning Alteryx and therefore couldn't transform it to my expected results. The only things missing from the output are the actual company names and company accounts. Could you advise what I can look into using to pull out this data? I was thinking of connecting it with the Record_ID i.e. Record_ID 1=Company Name 1? But then it's a bit risky as the data could move i.e if there are new companies added (which shouldn't be often)

I also forgot to attach the updated sample data previously but here it is.

Many thanks

Quennie

SAMPLE DATA.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703