Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Peek Function

HI,

I hope someone can help me out. I got the following table:

AccountNumOpeningDateOpeningAmountTransactionDateTransaction AmountClosingDateClosingAmount
111.222.33301-01-2012100----
111.222.333--01-01-201245--
111.222.333--01-01-2012100--
111.222.333----01-01-2012245
111.222.33307-01-2012245----
111.222.333--07-01-2012200--
111.222.333--07-01-2012100--
111.222.333----07-01-2012545

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

5 Replies
MVP
MVP

Re: Peek Function

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.

msheraton
Contributor III

Re: Peek Function

What does your data model look like?

Not applicable

Re: Peek Function

Untitled.png

Not applicable

Re: Peek Function

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

msheraton
Contributor III

Re: Peek Function

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.