DataFlow - Quickbooks - Picking up Edits

imelendez
imelendez Member
in Magic ETL

Good Morning, Experts!

 

I wanted to resurrect a question I had asked previously.

 

The question encompassed a dataflow that was built. In the data flow, the data flow is bringing in inputs from 4 separate Stacker jobs that aggregate data from dataflows from various Quickbooks (QB) jobs (Accounts, Vendors, Class and GL). In this master data flow, there is a rank and window taking place that is accounting for Date, ModifiedTime and TXNumber in descending fashion. Where we see a discrepancy in our data is with Balance Sheet Month details report. What our source of truth shows on QB is that some months are off for Total Cash, Total Accounts Payable, Total Credit Cards and Total Liabilities, while some months are spot on to the penny. What we have noticed is that this happens when there is a modification done to the entry during reconciliation monthly of the books. 

 

I have provided a screenshot of the rank and window items being looked at for the data. 

 

We have hit a wall because at this point we do not see the data missing, it is there, but the Account Ending Balance portion is not being displayed correctly due to out of sequence information. I am trying to see if there is knowledge of any other field that we can use to rank and window by or express logic differently to get it to spot on. There is a TxnID field but it seems like a ROW GUID and does not seem like it would help us. However, I could be wrong and on to something, but, I am hopefully relying on your expertise and help. 

 

Let me know if I can answer any questions or provide more feedback.

 

Thankful,

 

Isaiah Melendez

 

 

Best Answer

  • DataMaven
    DataMaven Coach
    Answer ✓

    As it turns out...The issue was due to the developer using the wrong fields as transaction identifiers.  The field used were sometimes null, and there were more robust options available.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • DataMaven
    DataMaven Coach

    It's tough without seeing the dataflow or datasets, but I feel like this might have something to do with null values. 

    Do the modifications done during reconciliation have TXNumbers?  If not, run a transform before your ranking, which fills all null values with a value. 

    If TXNumber is a text field, you could make this 'Recon' or 'Manual'. 

    If it's a number, perhaps 0 would work. 

    If this does not have null values, make sure to check the date and time fields.  Perhaps dates are entered, but not times.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • imelendez
    imelendez Member

    Hi, DataMaven!

     

    Thanks for your response. I really appreciate it.

    Could you expand on your hunch of this being a NULL value issue? I am intrigued to learn more about this.

    I do not think it is a NULL value issue but I could be wrong. I have attached 4 pictures showing 1) high-level balance sheet summary of a Month in 2018 2) domo card breaking down Total Cash 3) validation screenshot of JAN2018 showing the comparison in data points from DOMO and QB 4) transaction level detail including last modified date and txn #.

     

    Let me know if that sheds some light. If you are willing and able, I would love to pick your brain and let you look at the dataflow if need be to see if it also sheds some light.

     

    Thanks again!

  • DataMaven
    DataMaven Coach
    I'm looking over your attachments, but wanted to address the null value issue quickly. The bottom line - Domo HATES null values!

    I've had many times where I've had issues with them on cards. For example, I know I have had pie charts before where one segment is representing those records with null values, and when you drill, it says there is nothing there.

    In ETL, I have had calculations not calculate AT ALL if there is a null value. For example a sum of column 1 and column 2, where sometimes one of the column is null. Instead of treating like a zero, it will simply not return a value. This then makes any bottom line figures in a summary view incorrect.

    So - whenever I have something that mysteriously isn't behaving the way I expect, the first thing I do is check for null values. Next is leading and trailing spaces.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • imelendez
    imelendez Member

    I see, thank you for explaining that.

     

    I am working with a support manager at Domo to help us figure this out and he too is scratching his head like we are. I will bring up the NULL value issue to see if it sheds light on something.

     

    Let me know if the attachments provided a bit more clarity as to what I was saying.

  • DataMaven
    DataMaven Coach

    Looking at the info you sent, I am not thinking it's the null issue - though that's a good tidbit for you to keep in your back pocket!  

     

    I notice that one account's value is high, and another low, which I think eliminates 2 scenarios:

    1.  Null value issue - Would expect both variances to reflect a low result.  

    2.  Faulty join - Would expect both variances to reflect a high result.  

     

    It's possible that there are multiple issue, but I'm assuming that's not the case.  

    I'd be happy to look at the dataflow, too, and even hop on a webex if you are on with support.  I'm between employers at the moment, so I feeding my Domo addiction using the Dojo.  

     

    Check out my story on the story contest on the Dojo!  

    https://dojo.domo.com/t5/Domo-Dojo-Contest-Domopalooza/I-have-a-problem-and-Domo-is-NOT-the-solution/idi-p/38706

    (I should have added that to my signature when I first posted it!!!)

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • imelendez
    imelendez Member

    Cool story! Loved reading then putting the words into an image. I can relate, I too, am a workaholic. ?

    If you are interested I could start a Zoom meeting. Would love to learn from a pro. ?

    I appreciate your willingness to help.

  • DataMaven
    DataMaven Coach
    Answer ✓

    As it turns out...The issue was due to the developer using the wrong fields as transaction identifiers.  The field used were sometimes null, and there were more robust options available.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories