Constants Mapping

[Krish]

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

[MarqueeCrew · Accepted solution]

@Krish,

Yes you can!

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

[Krish]

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_TYPE	100
3:ACCT_STATUS	Incomplete

With the cross table output it converts as follows:

_1_RECORD_TYPE	3_ACCOUNT_STATUS
100	Incomplete

But I  need 

-1:RECORD_TYPE	3:ACCOUNT_STATUS
100	Incomplete

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

Thanks in advance.

[MarqueeCrew · Accepted solution]

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.

Cheers,

Mark

[Krish]

thank you for the explanation.

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

[MarqueeCrew]

@Krish,

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

Cheers,

Mark

[Krish]

Hi  Mark,

I will  start upgrading my version.

Tried the given steps. It worked as expected.

thanks a lot

Krish

[Krish]

I tried the steps given.

It worked as expected.

Thanks a lot.

Krish