Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
I hope someone can help me out. I got the following table:
AccountNum | OpeningDate | OpeningAmount | TransactionDate | Transaction Amount | ClosingDate | ClosingAmount |
111.222.333 | 01-01-2012 | 100 | - | - | - | - |
111.222.333 | - | - | 01-01-2012 | 45 | - | - |
111.222.333 | - | - | 01-01-2012 | 100 | - | - |
111.222.333 | - | - | - | - | 01-01-2012 | 245 |
111.222.333 | 07-01-2012 | 245 | - | - | - | - |
111.222.333 | - | - | 07-01-2012 | 200 | - | - |
111.222.333 | - | - | 07-01-2012 | 100 | - | - |
111.222.333 | - | - | - | - | 07-01-2012 | 545 |
The problem is when i select a date between the openingdates there will be nothing to show. I need to create something that when i select date 06-01-2012 it will show the OpeningAmount of 07-01-2012. The same for the ClosingDate, 06-01-2012 should show ClosingAmount 245.
Hope someone can give me some input how to do this! I've tryed useing the peek function but something is going wrong.
Marco
What does your data model look like?
Banktransacties:
LOAD
IF(Bank = 'Rabo', Left(AccountNumber, Len(AccountNumber)-3), AccountNumber) AS Rekeningnummer,
TransactionReferenceNumber,
StatementNumber,
TransactionValueDate,
IF(RowType = 'OpeningBalance', 'OpeningBalance') AS OpeningBalance,
IF(RowType = 'Transaction', 'Transaction') AS Transaction,
IF(RowType = 'ClosingBalance', 'ClosingBalance') AS ClosingBalance,
RowType,
OpeningBalanceDebitCredit & TransactionDebitCredit & ClosingBalanceDebitCredit AS DebitCredit,
OpeningBalanceDebitCreditFactor & TransactionDebitCreditFactor & ClosingBalanceDebitCreditFactor AS DebitCreditFactor,
OpeningBalanceCurrency & ClosingBalanceCurrency AS Currency,
OpeningBalanceAmount,
ClosingBalanceAmount,
TransactionAmount,
IF(OpeningBalanceAmount >0, OpeningBalanceAmount, IF(ClosingBalanceAmount >0, ClosingBalanceAmount, TransactionAmount))AS Amount,
OpeningBalanceDate,
ClosingBalanceDate,
TransactionDate,
TransactionDate AS CalendarDate,
IF(OpeningBalanceDate >0, OpeningBalanceDate, IF(ClosingBalanceDate >0, ClosingBalanceDate, TransactionDate)) AS Date,
TransactionEntryDate,
Right(TransactionType,3) AS TransactionTypeCode,
TransactionCounterAccountNumber,
TransactionAccountServicingInstitutionsReference,
TransactionNumber,
Description1, Description2 & Description3 & Description4 & Description5 & Description6 AS Description
// Bank Wordt uit het excel bestand geladen.
// DescriptionOrderingPart, LEEG
// DescriptionReference, LEEG
// DescriptionBeneficiary, LEEG
// DescriptionName, LEEG
// DescriptionAddress, LEEG
// DescriptionRemittance, LEEG
// DescriptionSettlementDate LEEG
FROM
[..\..\Data\QVD\DM_Banktransactions_MT940_v5.QVD]
(qvd);
TransactieTypes:
LOAD A AS TransactionTypeCode,
B AS TransactionType
FROM
[..\..\Data\xlsx\TransactionType.xlsx]
(ooxml, no labels, table is Blad1);
RekeningGegevens:
LOAD Rekeningnummer,
Bedrijfsnummer,
Bedrijfsnaam,
Bank,
Grekening,
IBAN,
BIC
FROM
[..\..\Data\xlsx\Bankreknr_Bedrijfsnr.xlsx]
(ooxml, embedded labels, table is Blad1);
I've re-read your opening post about 5 times and am still unsure what you want.
I need to create something that when i select date 06-01-2012 it will show the OpeningAmount of 07-01-2012. The same for the ClosingDate, 06-01-2012 should show ClosingAmount 245.
If you select 06-01-2012 why will are you expecting it to show other dates (ie 07-01-2012) That in itself does not make much sense. Unless you mean you want to show dates greater than 6-01-2012.
Also you need to work on your data model. Why do you have two types of date fields? Just create one and create a "transaction type" ie Opening, Transaction, Closing.
It's hard to be of more assistance unless you can explain better what you are trying to do.