Output Excel Format to be specific

[VJ_88]

1. We have 101 columns in excel on which we have developed Alteryx
2. We have naming standards for columns &NN is for columns that should have Number datatype and &DD for date Datatype
3. We have used multi field formula  formula
   • First Formula
        if (Endswith([_CurrentFieldName_],'&NN') AND ISEMPTY([_CurrentField_])) THEN -1 
             ELSEIF (Endswith([_CurrentFieldName_],'&DD') AND ISEMPTY([_CurrentField_])) THEN '1900-01-01'
             ELSE [_CurrentField_]
         ENDIF
   • Second Multifield Formula
       if Endswith([_CurrentFieldName_],'&NN') THEN TONUMBER([_CurrentField_]) 
       ELSEIF Endswith([_CurrentFieldName_],'&DD')  THEN TODATE(DATETIMEPARSE('1900-01-01',"%m/%d/%Y")) -- to                           change 
       ELSE [_CurrentField_]
     ENDIF
4. Now, these columns are written as output tool in an excel and as String
5. We need to store these as Date and Number but these are stored as General
6. Although toDate and toNumber is used still Select tool shows V_String and Excel output as General
7. Screenshots:

Kindly help

[apathetichell]

Output data - when creating a new file - or overwriting an unformatted sheet/range or overwriting a formatted range/sheet without 'retain formatting' checked - writes as general. This is normal.

[binuacs]

@VJ_88 one way of doing this with the dynamic select tool

[KGT]

One of the key things missed in the original formulas is that the field-type didn't actually change. So, the formula will act on the data, but then you need to actually change the metadata of the field either through a select or in the Multi-field formula (Change Output type to), which would mean splitting the date and number formula to 2 separate formulas updating the field type at the same time. 

The Dynamic Select in @binuacs response achieves this. I just want ed to highlight that as being the key difference in what you are trying to achieve. But as @apathetichell pointed out, if you are writing to a range rather than the sheet, you could get the driver doing different things with the datatypes as Alteryx will write fields, rather than cell by cell.

[VJ_88]

Hi @KGT ,

Do you suggest I should create new excel everytime rather than overwrite

Hi @binuacs ,

I tried Dynamic select using formula if FieldName Endswith.then TODATE(Field)

I have a question you have used JOIN after 02 dynamic select what is the join condition 

After Dynamic Select I wrote the data in EXcel (Overwrite sheet) and output is General Format even a select tool post dynamic select give V_String

[binuacs]

you can join using the join position option

for your second question make sure your date field is in the format of YYYY-MM-DD which will write the date field in the excel, you can refer the workflow i provided

[VJ_88]

Thanks @binuacs  for the time and effort.

Although I tried Dynamic select and it filters all date column in 01 branch and second Dynamic select all Integer column third rest 

Used toDate in multifield post dynamic select and toNumber for Interger columns

post that I used a Select tool as still the output excel was all  column generic format, even select showed everything as V_String

[KGT · Accepted solution]

On your Multi-field formula, have you selected to change the field types? Bottom half of this screenshot. ToDate() will change the data, but not the metadata. This will change the metadata.