2 Replies Latest reply: Nov 25, 2017 4:24 AM by Stephanie Hallberg RSS

    Use String Function Keepchar and Intervalmatch

    Stephanie Hallberg

      So, we have a problem with Numbers in Excel file, (bankaccount.xls) that we need to use in Qlikview

       

      Bankaaccount has four columns, TypeOfAccount, Description, TotIncome, BankNumber as this.

      TypeOfAccount
      DescriptionTotIncomeBankNumber
      Summary
      Sum of All50000
      B1000-3000;B3000-3500
      Loan
      Loan account600000
      B2000-3000;B4000-4100

       

       

      The important column, BankNumber looks like this: B1000-3000;3500-3700

       

      For the task we need to create a row for each bank number, e.x. 1000, 1001, 1002, 3501 etc.
      So at the start it looks like this. We use the String Function - Purgechar to remove the B.

       

      BankAccount:

      LOAD

      TypeOfAccount,

      Description,

      TotIncome,

      BankNumber , PurgeChar ( BankNumber, 'B' ) as NewBankNumber

      FROM

      [..\Desktop\Book1.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Resulting in 1000-3000;3500-3700.

      Then we use SubField to separate the start and end account numbers, inside a resident load.

      BankAccountNew:

      LOAD

      NewBankNumber,

      SubField(NewBankNumber, ';',1) as FirstAccount,

      SubField(NewBankNumber, ';',-1) as SecondAccount

      Resident

      BankAccount;


      Resulting in A separate table "BankAccountNew" that holds the start and end account number

      StartEnd
      10002000
      20004100



      I believe we need to use Intervalmatch to create each bank account 1000,1001,1002 etc.. but here I not sure how to write the Intervalmatch function.

       

      Can anyone help me?

       

       

       

        • Re: Use String Function Keepchar and Intervalmatch
          Antonio Mancini

          May be this

          Temp:
          LOAD TypeOfAccount, Description, TotIncome,SubField(PurgeChar(BankNumber,'B'),';') as BankNumber Inline [
          TypeOfAccount,Description, TotIncome,BankNumber
          Summary,Sum of All,50000,B1000-3000;B3000-3500
          Loan,Loan account,600000,B2000-3000;B4000-4100]
          ;
          NoConcatenate LOAD TypeOfAccount, Description, TotIncome,
          SubField(BankNumber,'-',1)+IterNo()-1 as BankNumber
          Resident Temp
          While SubField(BankNumber,'-',1)+IterNo()-1 <= SubField(BankNumber,'-',2);
          Drop Table
          Temp;

          Regards,

          Antonio

            • Re: Use String Function Keepchar and Intervalmatch
              Stephanie Hallberg

              Thanks for replying Antonio


              From your teachings,I  learned abit about using functions in the same table. And how the IterNo()-1 and While works.

              Thanks lovely,

               

              And I see my version of Qlikview (PE) can only handle a maximum amount of numbers..
              Meaning, removing one 0 from each value .. 1000 becomes 100, 3000 becomes 300, 3500 becomes 350 etc.
              Then all values are showing.

               

              Thanks for the solution