Peek Function

Wed, 26 Jun 2013 07:09:40 GMT

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

Re: Peek Function

tresB — Wed, 26 Jun 2013 12:14:58 GMT

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.

Re: Peek Function

RedSky001 — Wed, 26 Jun 2013 14:00:13 GMT

What does your data model look like?

Re: Peek Function

Thu, 27 Jun 2013 08:10:39 GMT

Re: Peek Function

Thu, 27 Jun 2013 08:14:49 GMT

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

Re: Peek Function

RedSky001 — Thu, 27 Jun 2013 09:32:08 GMT

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.

topic Peek Function in QlikView

Peek Function

Re: Peek Function

Re: Peek Function

Re: Peek Function

Re: Peek Function

Re: Peek Function