Issue w/Preceding Load, Select from Two SQL Tables

[#1]

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 ------

[bgerchikov]

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.

[#3]

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!

[bgerchikov]

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!

[#5 · Accepted solution]

Hey Nicole,

Just some suggestions that I can think of.

• First just do a preload(without any transformations) Extract Layer

Table1:

Load *;

SQL Select *

From DataBase;

Store Table1 into Table1.qvd(qvd);

• Then do your transformations, formatting, all other functions in Transform Layer by reading from those qvds
• Also by looking at your two qvds it doesn't look like a good idea to join the two tables at all as the resultant table will cause duplicate records. The key is not unique enough.
• Any reason why you wanna join the two tables?

Thanks

AJ

[#6]

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:

• 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]

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!

[#7]

Nicole - a few more suggestions

• Every GP Acct has a unique AcctIndex and also credits and debits are in the other table Balances. So why do you want to do them in Transaction table. I would do it in Balances table using AcctIndex like

IF(AcctIndex =Previous(AcctIndex ), Previous(Debits)-Previous(Credits),0) as [Opening]

• I dont understand the Balance formula used. Can you please explain the logic of that(this can also be done in Balances table).
• Also whenever you use previous() you need to use Order by function for correct results.

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.

[bgerchikov]

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

[#9]

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

[#10]

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!