Alteryx formula missing some values

[aiubxh8]

I have set up a formula to capture the start date ("ArtWorkStartDate"), which requires looking at 3 different dates. There are just over 4300 rows in the output, and this formula worked as I had hoped for all but 4 line items.

The 4 line items are shown above, as well as the formula. As you can see in the output, the ArtWorkStartDate should have been populated with the values in the AltStartDate column (for the first 3) and the TransStartDate column (for the last one) based on the formula. These are the only 4 records with this issue.

This may be something deeper in the database I'm working with, but are there any suggestions / thoughts as to what could be causing this error?

[BenMoss]

Is the date structure on these four lines fundamentally different to the rest of the data? that would be my first port of call?

Can you share the workflow and data also, this will help us diagnose the problem. Perhaps you can sensitize it if required.

[BenMoss]

Perhaps the value in the first column is not actually NULL in this case and instead a blank?

[aiubxh8]

I have been taking a look at the data structure and have yet to find an inconsistency, but I am still considering that.

The data source is a database that I'm connected directly to. I have attached the workflow. I am not sure if you are able to view it since you do not have access to the input.

[aiubxh8]

That is a very good suggestion. I am going to take a look at that. Thank you

[aiubxh8]

I had marked as solution accidentally - still working on finding a solution. After taking a look, it seems those fields are in fact Null (rather than blank).

[SeanAdams]

Hey @aiubxh8,

Working with strings can be a bit tricky because as mentioned above, they can have blanks or single spaces in them - but I think that the issue is blanks vs. nulls (as @BenMoss said)

What I'd suggest is:

- Before this formula, first use a multi-field formula tool which checks for any blank strings and converts them to null.   That formula is really quick, and you should select ALL of your text fields to apply to this

iif(
      isempty(trim([_CurrentField_]))
      ,null()
      ,trim([_CurrentField_])
)

If you still have trouble - put an output tool before your formula and capture the data at that point, and reply to this thread with that data-set, and we can quickly iterate with you to an answer.

Then you should get clean results from your existing formula which I've reformatted for readababity without having to do an isEmpty(trim(...)) check on every field too.

IF
      !(ISNULL([ELCCollabStartDate])
      or isempty(trim([[ELCCollabStartDate]])))
THEN
      [ELCCollabStartDate]
ELSEIF
      ISNULL([ELCCollabStartDate])
      AND !ISNULL([ELCApprovalStart])
THEN
      ([ELCApprovalStart])
ELSEIF
      ISNULL([ELCCollabStartDate]) AND ISNULL([ELCApprovalStart]) AND !ISNULL([TransStartDate])
THEN
      [TransStartDate]
ELSEIF
      ISNULL([ELCCollabStartDate]) AND ISNULL([ELCApprovalStart]) AND ISNULL([TransStartDate])
THEN
      [AltStartDate]
ELSE
      ' '
ENDIF

Cheers @aiubxh8

Sean

[aiubxh8]

Thanks, @SeanAdams

I tried adding in the multi-field formula tool to remove the blank strings, but I am still having some trouble. I have attached my output before that multi-field formula tool.

[NicoleJohnson · Accepted solution]

Hi!

Looked into your data a little more, and have a question about your formula... The ELCApprovalStart field that you're using in your formula isn't a date field, it's showing 0's & 1's... the 4 results that you're seeing that don't line up with your expectations are the 4 in your dataset that have this field value showing as 0. So in that case, in the formula below where I have it highlighted in bold blue, the result of your nested if statements is giving you a the value of ELCApprovalStart where ELCCollabStartDate is null and ELCApprovalStart is not null, which in those 4 cases is giving you a result of 0 (which in a date field shows up as 1/0/1900). So I think it's perhaps that you have the wrong field after the THEN clause in your nested IF statement where highlighted below... or maybe it should be ELCApprovalStart = "1" or something like that, to rule out both nulls and 0's. Does that seem like it might make sense/be the case? Is a different date field supposed to be used here in your formula?

IF !ISNULL([ELCCollabStartDate]) THEN [ELCCollabStartDate]
ELSEIF ISNULL([ELCCollabStartDate]) AND !ISNULL([ELCApprovalStart]) THEN ([ELCApprovalStart]) ELSEIF ISNULL([ELCCollabStartDate]) AND ISNULL([ELCApprovalStart]) AND !ISNULL([TransStartDate]) THEN [TransStartDate]
ELSEIF ISNULL([ELCCollabStartDate]) AND ISNULL([ELCApprovalStart]) AND ISNULL([TransStartDate]) THEN [AltStartDate]
ELSE ' ' ENDIF

Let us know if that was the case. Sometimes just takes a few more eyeballs to spot the culprit :)

NJ

[aiubxh8]

Thank you, that explains the issue. Much appreciated!

Best,

Ellie