Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

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

Dynamically Change the data type

Sshasnk
8 - Asteroid

So I have a dataset which I want to change it dynamically.

 

Input:

E_Nameaccountphonecheck
Alex1235768491
leo8903450
    

 

 

For the above data I want to change it with the below type and size.

 

NameTypeSize
E_NameV_WString167
AccountDouble

8

phoneInt6434
checkDouble8

.

 

So for an example, If E_Name type is V_string so we have to change it to V_WString and if size is 255 I have to change it to 167 (dynamically)

 

9 REPLIES 9
alexnajm
17 - Castor
17 - Castor

Could you use the Auto Field tool?

Sshasnk
8 - Asteroid

@alexnajm  Actually in every run I have to change the data type, and check if it matches to the below data type

alexnajm
17 - Castor
17 - Castor

As long as the name remains the same then, a Select tool should work fine!

Sshasnk
8 - Asteroid

@alexnajm Umm no actually, the data type list I mentioned above is coming from different source and I have to keep constant. Plus I have 250 fields and I have to convert it every time in the select tool.

alexnajm
17 - Castor
17 - Castor

I’m not sure the problem is clear then - can you explain further? Having your workflow plus data would help us see the issue. 

From the description above, it sounds like you just need to resize fields to which the Select tool will work. Once you set it once, you can copy and paste the same Select tool to keep the configuration and apply it to a different source. 

Sshasnk
8 - Asteroid

@alexnajm  Hi Sorry, I could not explain the issue. I cant share the worklfow because of the confidentiality reasons.

But let me break down the issue for you.

 

1. I have a data set from excel which has 250 fields, Which I want to push it into the database.

2. My data gets errored out because the data type in the database is different and my data has different data types.

3. I cant use select tool because I dont want to do this in every run because the db team changes something is the back end I get an error and I have to change the data type for all the data.

4 So to resolve this I am calling the DB (Input tool) and checking the database data type (using info tool) <- Once I get the data type I want to replicate the same data type in my data. So it wont error out.

 

So, if you see the second table that is an example which I am getting DB info (Step 4)

 

I hope this helps 

danilang
19 - Altair
19 - Altair

Hi @Sshasnk 

 

All the config info of the various tools is stored as XML, so we just need to build the correct XML for a Select tool, using your field types and sizes and then pass this XML as a Control Parameter to macro.  That's what the top branch of the workflow does.  

 

danilang_1-1678628735589.png

 

Inside the macro, the Action tool uses the Update Raw XML with Formula to update the entire xml of the action tool.  

 

 

danilang_0-1678628670634.png

 

Here are the type and sizes of the fields in the Data before and after the Macro

BeforeBefore

AfterAfter

 

In your case connect the output of your Field Info tool to first formula in the top branch.  The only issue is your data type input shows the length of an Int64 as 34, but in most data bases it is 8.

 

Dan

 

 

apathetichell
19 - Altair

while there's no real reason not to use @danilang 's suggestion - if you are scared of editing the raw XML because you were terrorized as a young child by an evil Uncle who forced you to endlessly build SOAP API calls - you could do some dynamic changes in a batch macro as attached. note the output has to be unioned together in a way I personally find annoying. I've thrown in some dynamic select tool and summarize tools to drop the offending nulls. 

Lichunhuang
7 - Meteor

Great post! 

The workflow you provided is extremely helpful! 

I am able to change the datatype without changing the order of the column!! 

Labels
Top Solution Authors