Alteryx formula missing some values

2017-05-22 11_01_34-Duration.xlsx - Excel.png

2017-05-22 11_04_31-Alteryx Designer x64 - Artwork Metrics_Virtual Ticket Workflow.yxmd.png

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?

Common Use Cases

Accepted answers

NicoleJohnson

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

All comments

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.

ArtworkMetricsFlow.yxmd

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).

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703