topic Re: Incorrect Join in data load editor in App Development

Incorrect Join in data load editor

mijileee — Sat, 16 Nov 2024 06:44:59 GMT

Hello,

I'm trying to figure out how to bring in the gaps for the dimension "Branch". Branch is in a header table while the GL Detail Account Number and Account Desc Category are in a detail table.

So, I thought doing a left join into the detail table would resolve this issue but I'm still seeing these gaps.

[GL Detail]:
LOAD
bga_acct_cl as [Account Class Code Key],
bga_bsc_gl_acct as [GL Detail Account Number],
Pick(left(bga_bsc_gl_acct,1), 'Asset', 'Liability', 'Equity',
IF (left(bga_bsc_gl_acct, 2) < 43, 'Revenue', 'Expense'),'Expense', 'Expense', 'Revenue', 'Expense')
as [GL Account Desc Category]
FROM [lib://QVD Files/Extract/GLDetail.qvd]
(qvd);

Left Join([GL Detail])
Load
gld_bsc_gl_acct as [GL Detail Account Number],
left(gld_sacct,3) as [Branch]
FROM [lib://QVD Files/Extract/GLTransactions.qvd]
(qvd);

Any insight or help to resolve this would be greatly appreciated!

Thank you!

Re: Incorrect Join in data load editor

vishsaggi — Fri, 25 Jan 2019 16:14:43 GMT

Not sure I quiet understood your requirement. Can you elaborate with some sample data and what your expected output would be. You can try and Inner Join or Just don't use any Join load two tables and let QV joins them based on common key. Check and let us know.

Re: Incorrect Join in data load editor

mijileee — Fri, 25 Jan 2019 19:17:00 GMT

Hi,
Unfortunately, the data I'm using is for a client so I cannot post their information due to privacy reasons. The expected output should show "Branch" with "CLA" for every account number.

Re: Incorrect Join in data load editor

dplr-rn — Fri, 25 Jan 2019 19:41:45 GMT

From a quick look it looks to me like there is data missing for areas where the gaps come. hard to know for sure without seeing data.
to test; remove the join statement in your script and check output i.e. you have 2 tables [GL Detail] and BranchDim

Re: Incorrect Join in data load editor

vishsaggi — Fri, 25 Jan 2019 22:36:33 GMT

You do not have to share your original data just try to scramble your data and share if possible. BTW did you try my suggestions to remove the Join and load tables as is and check?

Re: Incorrect Join in data load editor

mijileee — Wed, 30 Jan 2019 23:14:42 GMT

Thank you for the help! Your suggestion worked out to help me resolve the issue!