Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Constants Mapping

Krish
8 - Asteroid

I have around 100 hardcoded fields like in the following format (in excel file)

 

Field  Value

 

abc   100

xyz    200

 

with the text input pointing to this excel Can I map the values against another set of column in formula expression?

 

Thanks,

Krishna

 

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus

@Krish,

 

Yes you can!

 

Capture.PNG

The upper input has 3 records with data.  The lower input has 100 field value pairs as you described in your post.  What I've done is used a CROSSTAB to turn the 100 values into a single row.  I then append those 100 fields to EVERY record.  Now you can use a formula and reference the data.  Later you can SELECT and remove those fields.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Krish
8 - Asteroid

Thanks for the update.

 I could flip the table as needed.But problem is with the renaming of fields.

 

Like 

I have source table like this:

 

-1:RECORD_TYPE100
3:ACCT_STATUSIncomplete

With the cross table output it converts as follows:

 

_1_RECORD_TYPE3_ACCOUNT_STATUS
100Incomplete


But I  need 

 

-1:RECORD_TYPE3:ACCOUNT_STATUS
100Incomplete

 

 I have seen options like Dynamic rename and Regex .But I am little confused why - is converted to _

Thanks in advance.

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

Krish,

 

I solved that with a dynamic rename.  I didn't use the RegEx path, but used the rename from rows of data (table).  I created a table of converted (_) names and original fields using a POSITIONAL join.  That table of changes was used in the rename tool to reverse the naming.  For anyone using a crosstab who does not want to see the underscores replacing their spaces or special characters, this could work.

 

Capture.PNG


Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Krish
8 - Asteroid

thank you for the explanation.

I could not open sample solution throws error:" Was created by recent version and cannot be read"

MarqueeCrew
20 - Arcturus
20 - Arcturus

@Krish,

 

You're using an older version (10.x) of Alteryx.  You are missing out!

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Krish
8 - Asteroid

Hi  Mark,

 

 

I will  start upgrading my version.

Tried the given steps. It worked as expected.

thanks a lot

 

Krish

Krish
8 - Asteroid

I tried the steps given.

It worked as expected.

 

Thanks a lot.

 

Krish

Labels