In-DB Tools-Fill Down Data

[KrisManns]

Hi Everyone,  I am pulling data from Snowflake using the Alteryx In-Database tools.  The dataset I am pulling only shows a reference number in the first row of a journal entry.  I want to duplicate this data to the other null rows.  Can I use the Formula In-DB tool to copy down the Reference # to the other rows of the sample journal entry shown below?  I know I can use a Multi-Row Formula Tool but I prefer to use an In-DB tool.  Thank you!

Journal Entry #     Journal Entry Line #       Reference #

123456789                          1                             ABC

123456789                          2                            (null)

123456789                          3                            (null)

This is what I want the dataset to look like

Journal Entry #     Journal Entry Line #       Reference #

123456789                          1                             ABC

123456789                          2                             ABC

123456789                          3                             ABC

[StellaBon]

@KrisManns Is it all the same reference number? If it's fairly static you could do something like, Formula In-DB to create a new column with that reference number. Or if there are only a few reference numbers use a Formula in-db with a conditional statement.

Create New Column--> Name Column--> enter text you want to display: 'ABC'

IN-DB formula Conditional Example: If ISNULL( [reference number]) then 'ABC' ELSE [reference number] ENDIF

Another Example: IF ISNULL(reference number]) AND [Journal Entry] = '123456789' THEN [reference number] = 'ABC' ELSE 'DEFGH' ENDIF

Otherwise create a lookup file for multiple conditions with a reference number in each column, join it and reference each column in your conditional statement. Hope this helps!

[jrgo]

@KrisManns 

You can apply Window Functions using the Formula In-DB tool. In this case, the function you'd use is LAG.

The function would be something like this:

LAG("Reference #") OVER (PARTITION BY "Journal Entry #" ORDER BY "Journal Entry Line #")

Note that this is something that you'd include in a CASE statement to first evaluate if your reference field is NULL, similar to what you'd need to define in Alteryx's Multi-Row Formula tool.

[jdminton · Accepted solution]

Since you have the journal entry number and line number, you can do a summarize in DB (JE, Reference) and join that back on the original data with a join on JE to create a reference for every line from the summarized side. You might want to validate that no JEs have multiple references included. If there are, you want to make sure that you are using the correct logic. You could also use First on the reference field so that only one exists if you are sure that they should all have the same reference.

[apathetichell]

@StellaBon - FYI Formula In-DB uses native to the DB functions:

If ISNULL( [reference number]) then 'ABC' ELSE [reference number] ENDIF

would not work. You would use:

CASE WHEN "reference number" IS NULL then 'ABC' ELSE "reference number" END

[KrisManns]

Thank you all for the suggestions!  My analysis so far is showing that there is only one reference number per journal entry #.  I am using jdminton's solution in my workflow and it works.  Thanks again!.