Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
tresesco
MVP
MVP

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
Partner - Creator III
Partner - Creator III

What does your data model look like?

Not applicable
Author

Untitled.png

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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.