Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Sample tool - inconsistent data

qgpl
8 - Asteroid

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

11 REPLIES 11
ChrisTX
16 - Nebula
16 - Nebula

For data like this, not fully structured, the challenge is to develop rules that can always be relied upon, to identify field names and field values.

 

After the word "Balance", it looks like:

  • If the next row is a number, then it represents the value for Balance
  • If the next row is not a number, then it represents the value for Company Name
  • After a Company Name, the next row will always be a value for IBAN

 

I don't fully understand these "rules" for your data, so the best I can do is guess.

 

Once you develop rules like this, use a Formula tool or  Multi-row tool to clearly identify each "value" in your original data as either a Field Name or a Value.

 

Chris

qgpl
8 - Asteroid

Thank you, Chris! I will see where I get up to. It's frustrating because the data is so inconsistent. 

Labels
Top Solution Authors