Peek Function

Report Inappropriate Content · ‎2013-06-26

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

tresesco · ‎2013-06-26

Temp1:
Load * from yoursource;

NewTable:
Load
     Date,
     YourField,
     If( IsNull( YourField ), Peek( YourField_New ), YourField ) as YourField_New
Resident Temp1
Order By Date;

You have to try something like above.

If this does not resolve your problem, you can share your sample application.

Thanks.

RedSky001 · ‎2013-06-26

What does your data model look like?

Report Inappropriate Content · ‎2013-06-27

Report Inappropriate Content · ‎2013-06-27

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

RedSky001 · ‎2013-06-27

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.

Related Topics