Best Of

For MySQL use the backtick character ` (not apostrophe- that creates a string) surrounding your column names.

ie

SELECT
`trade show`
,`contact person`
FROM
my_data_flow

That should also turn the name of your column light blue so you can tell that it worked.

Hi Skhatri, that certainly is a limited ad platform dataset. You can join header data to the daily ad rows even though they're only associated by row placement, but it's heavy on the assumption that these sort of daily rows repeat in this same fashion throughout the input.

Here is my start dataset and output dataset:

Here are the transform steps:

1 - set a constant of 1 (this is used in both the Row Number function and the join)

2 - use Rank & Window tile to get RowNum based on the constant 1.

3 - split the headers and daily rows into two opposing filters. Based on your sample I looked for the "-" character in the Campaign column.

4 - in the top header path, do a LEAD function on RowNum. Now you have the header's original RowNum as well as the RowNum associated with the next header.

5 - the HeaderLead of the last header will be null, so set it to something wildly high for your purpose (ex. 1,000,000). That way you have something to filter between later.

6 - join header and daily rows back together on the constant 1. This is a lazy/greedy join that puts all daily rows on all headers.

7 - in the last filter, include rows where the daily RowNum is greater than the Header Start Row number AND daily RowNum is less than HeaderLead value. That's saying, only keep daily rows that are between their header and the next header.

8 - any column cleanup, rearrange, you want in the last select tile. Might need to change your date from a string into a date too depending on how that came in from your input.

Couple key screenshots along with the whole transform layout -

Here's step 5, shows how you get a range on the header that can be used to filter down the day rows:

Step 6, showing the daily RowNum in between the header start (the header's original RowNum) and header lead values. You can see for the last ad/campaign header I set it's lead value to 1,000,000 to allow the filter to work.

Every analysts favorite game... Spot the disparity!

Do you have any duplicate, or blank loan_ids?

Did you try Full Outer Join?

Have you confirmed that your input datasets are exactly identical as the inputs for your excel file?

Does your excel file possibly have manually entered data?

Lastly, identify exactly which loan_ids are in the output in Domo, compare that to your Excel sheet, then if your Excel sheet has loan_ids that exist but are not in the Domo output... query your Domo source tables for those loan_ids.

It turns out that I did not need to manually alter the table that I was importing into. I changed the SQL in my import for the table to pull from the new data source and tried putting the new UUID values directly into the existing integer field. The Domo importer was smart enough to detect that non-integer values were being added to the integer field. It automatically changed the data definition for the identifier column from an integer to a string to accommodate the UUID values I started passing in.