Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create calculations that require me to pull fields from two tables, but am receiving an error (the calculations are highlighted in red below). Also, it would be great to have all of this data in one table to save as a QVD. Any tips or suggestions?
OLEDB CONNECT TO [...]
//-------- Start Multiple Select Statements ------
LOAD
[AcctIndex],
[PeriodID],
[Debits],
[Credits],
[Net]
;
SQL SELECT
[ACTINDX] as [AcctIndex],
[PERIODID] as [PeriodID],
[PERDBLNC] as [Net],
[CRDTAMNT] as [Credits],
[DEBITAMT] as [Debits]
FROM CMH.dbo.GL10110;
LOAD
[Account Number] as [GP Acct],
Left([Account Number],4) as [GP CC],
Right([Account Number],4) as [GP Class],
[TRX Date] as [TrxDate],
Month([TRX Date]) as [TrxMonth],
Year([TRX Date]) as [TrxYear],
Day([TRX Date]) as [TrxDay],
Today() as [Today],
Today()-1 as [Yesterday],
Month(Today())-1 as [Prior Month],
Year(Today())-1 as [Prior Year],
[Open Year] as [GL Year],
[Account Description] as [Description],
[Account Index] as [AcctIndex],
[User Defined 1] as [CPSI Acct],
Right([User Defined 1],3) as [CPSI CC],
Left([User Defined 1],5) as [CPSI Class],
IF([Account Number]=Previous([Account Number]), Previous(Debits)-Previous(Credits),0) as [Opening],
IF([Account Number]=Previous([Account Number]), RangeSum((Debits-Credits),Peek('Balance')),Debits-Credits) as [Balance]
;
SQL SELECT
[Account Number],
[TRX Date],
[Open Year],
[Account Description],
[Account Index],
[User Defined 1]
FROM CMH.dbo.AccountTransactions;
//-------- End Multiple Select Statements ------
Hey Nicole,
Just some suggestions that I can think of.
Table1:
Load *;
SQL Select *
From DataBase;
Store Table1 into Table1.qvd(qvd);
Thanks
AJ
Hi Nicole,
The statement you show doesn't look like preceding load. Let's see:
The first LOAD is a straightforward SQL statement which generates QV table. It works properly, correct? The second LOAD statement is also SQL selection where you're trying to use fields from the first one. However you're missing the part, that these statement are independent from each other. You need to join the data from both LOAD statements in one table first and only then perform additional operations.
The correct formula for preceding load is:
TempTable:
<1st SQL LOAD>
join
<2nd SQL LOAD>
;
Load *,
<Your statements are here>
;
Load
<Your Fields are here including statements>
Resident TempTable
/
Hope it will help.
Hi Boris Gerchikov: When I joined the two SQL tables together (I tried all of the joins!), the data was incorrect. I tried left, right, inner and outer join. Is there anything else I can try??
Also, for the two LOAD statements at the end, I would include (IF([Account Number]=Previous([Account Number]), Previous(Debits)-Previous(Credits),0) as [Opening], IF([Account Number]=Previous([Account Number]), RangeSum((Debits-Credits),Peek('Balance')),Debits-Credits) as [Balance]) first and then the fields from the two joined tables?
Thank you for responding so quickly!
Hi Nicole,
You're joining both table by [AcctIndex] - I didn't find any other fields. If the first SQL is the header data and the second one is details, make sure this field is unique. After join please review your data to make sure it was loaded and joined correctly and only then sort your table by [AcctIndex] or [Account Number] and apply your IF statement in the separate statement.
If you can please attach your sample data as qvds.
Regards!
Hey Nicole,
Just some suggestions that I can think of.
Table1:
Load *;
SQL Select *
From DataBase;
Store Table1 into Table1.qvd(qvd);
Thanks
AJ
Hi Ajay Prabhakaran: Thank you for your input - I am receiving duplication of data with the join. I need to reference fields from the two tables in order to create some calculations, like:
Would it work if I saved those two tables into QVD files and then created a load statement from both those QVD files?
Thanks again!
Nicole - a few more suggestions
IF(AcctIndex =Previous(AcctIndex ), Previous(Debits)-Previous(Credits),0) as [Opening]
Update: Also if you could attach those new qvds from extract layer I can try to work on those for addressing your issue with Opening and Balance formulas.
Hi Nicole,
I'm not quite sure what you're trying to achieve. In Balances table you have the data on Account - PeriodID level, in Transactions you have it on the Account-Month level. Are PeriodID and TrxDate related to each other? If not, you need to summarize Balances and join them to Transactions. (Again, it's just assumption that you need to show overall balances in Transactions, but most likely I'm wrong)
Directory;
Balances:
LOAD AcctIndex,
PeriodID,
Net,
Credits,
Debits
FROM
Balances.qvd
(qvd);
Transactions:
LOAD [GP Acct],
[GP CC],
[GP Class],
TrxDate,
[GL Year],
Description,
AcctIndex,
[CPSI Acct],
[CPSI CC],
[CPSI Class]
FROM
Transactions.qvd
(qvd);
Left Join (Transactions)
LOAD
AcctIndex,
// PeriodID,
SUM(Net) as Net,
SUM(Credits) as Credits,
SUM(Debits) as Debits
Resident Balances
Group By AcctIndex
;
DROP Table Balances;
Add IF statements here
You're absolutely correct - I didn't think of using the Account Index instead of the Account Number! Sorry, that was such an easy solution and I didn't see it!
I really appreciate your help - I'm new to scripting
Hello Ajay Prabhakaran: I ended up getting the expressions for Opening and Balance to work, but they aren't correct, unfortunately. Those expressions were recommended by another user.
I'm trying to calculate the "Opening" balance of the current period (which, for periods 1-12, is the "Period Balance" of the previous period). I am also trying to calculate the "Period Balance" by period, which accumulates for periods 0-12. What I'm looking for is best demonstrated by the formatting here: Opening Balance.
I would like to calculate the Opening and Period Balance in the script because they change if the user interacts with them on the sheet object.
Thank you!