5 Replies Latest reply: Jun 27, 2013 5:32 AM by Mark Sheraton RSS

    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

        • Re: Peek Function
          Tresesco B
          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
            Mark Sheraton

            What does your data model look like?

                • 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);
                  
                  
                    • Re: Peek Function
                      Mark Sheraton

                      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.