8 Replies Latest reply: Jul 13, 2016 10:04 AM by Massimo Grossi RSS

    How to calculate Statement from a list of records?

    Fabio Kourennoi

      Hello Everyone

      I have a doubt of which one is the best option to build a Statement from a list of bank records.

       

      Let's say I have

       

      Name Bank                    Date                        Amount         

       

      Deutsche Bank               31/12/2015                    200          

      Deutsche Bank               07/01/2016                    -100              

      Deutsche Bank               15/03/2016                      50              

      Deutsche Bank                29/03/2016                    -120   

       

      So my question Should I first calculate the statement  in the set analysis for every day of the calendar, store the data and reload so I can use this new calculate field to have the right statement

       

      Or I can calculate the statement in the Pivot Table?

       

      What I want is that for each bank and every day of the calendar the amount show is the sum of the previous records.

       

      Ex:   Deutsche Bank 01/01/2016 = 200

                31/01/2016 = 100

                20/02/2016  =100

                 31/03/2015 = 30

                   30/06/2016    = 30

      Thanks in advance

      f

        • Re: How to calculate Statement from a list of records?
          Anil Babu Samineni

          Fabio,

           

          Is this possible to share the App

           

          As i know, it is possible in Oracle Trigger command. If you are the oracle developer please create one trigger for that and then call that trigger to Qlik. Might be helpful

           

          - ANIL

          • Re: How to calculate Statement from a list of records?
            Marcus Sommer

            You could solve such a case by creating a time-period with From - To per interrecord-functions like Peek() or Previous() ? and afterwards you populate the date with a while-loop.

             

            t1:

            load Bank, Date as ToDate, previous(Date) as FormDate, Amount

            resident YourFirstLoadOfTheseData order by Bank, Date desc;

             

            t2:

            load Bank, date(FromDate + iterno() - 1) as Date, Amount

            Resident t1 while FromDate + iterno() - 1 <= num(ToDate);

             

            - Marcus

            • Re: How to calculate Statement from a list of records?
              Massimo Grossi

              an idea in the attachment, script is

               

              B:

              load * inline [

              Name Bank,                    Date,                        Amount        

              Deutsche Bank,               31/12/2015,                    200         

              Deutsche Bank,               07/01/2016,                    -100             

              Deutsche Bank,               15/03/2016,                      50             

              Deutsche Bank,                29/03/2016,                    -120  

              Bank,               05/01/2016,                    2000         

              Bank,               07/01/2016,                    -100             

              Bank,               15/03/2016,                      50             

              Bank,                29/03/2016,                    -120  

              ];

               

              C:

              load   date(makedate(2015,12,31) + rowno()-1) as Date AutoGenerate 366;

               

              join (C) LOAD Distinct [Name Bank]

              Resident B;

               

              Left Join (C) LOAD * Resident B;

              DROP Table B;

               

              FINAL:

              LOAD

                [Name Bank],

                Date,

                If([Name Bank]=Peek('Name Bank'), Alt(Amount,0) + Peek('CumAmount'), Alt(Amount,0)) as CumAmount,

                Amount

              Resident C

              Order By [Name Bank], Date;

               

              DROP Table C;

              • Re: How to calculate Statement from a list of records?
                Fabio Kourennoi

                HI

                I don't know why the attachment button  it doesn't show

                 

                That's the script I'm using

                 

                 

                 

                B:

                LOAD Conto,

                     dt_reg, 

                     [Nr Reg],

                     [Imp Ope],

                     [Descr Top],

                     F6    

                FROM

                [C:\Users\fkourennoi\Desktop\Elenco movimenti_2015.xls]

                (biff, embedded labels, table is [Elenco movimenti_2015$]);

                 

                 

                C:

                load   date(makedate(2014,12,31) + rowno()-1) as dt_reg AutoGenerate 366;

                 

                join (C) LOAD Distinct [Conto]

                Resident B;

                 

                Left Join (C) LOAD * Resident B;

                DROP Table B;

                 

                FINAL:

                LOAD

                  [Conto],

                  date(date# ([dt_reg],'DD/MM/YYYY'), 'DD-MM-YYYY') AS dt_reg,

                  If([Conto]=Peek('Conto'), Alt([Imp Ope],0) + Peek('Saldo'), Alt([Imp Ope],0)) as Saldo,

                  [Imp Ope],

                  [Descr Top] 

                Resident C

                Order By [Conto], dt_reg;

                 

                DROP Table C;

                 

                And this is the table

                The 3rd of January it doesn't show anything in a pivot table and two option on the Saldo table.

                      

                Contodt_regNr RegImp OpeDescr Top
                DEUTSCHE 03/01/201500000180441384,73INCOME
                DEUTSCHE 03/01/20150000018746-0,8EXPENSE
                DEUTSCHE 02/04/201500000187473259,2INCOME
                DEUTSCHE 31/12/20140000018738-12679,08SDD