7 Replies Latest reply: Oct 12, 2012 9:47 AM by AK C RSS

    How To Solve This Scenario On Script

      Dear All,

       

      I am trying to find out outstanding / Account Recivables details on script.

      For this I have 2 tables in first table I have customers debit balance on a particular date - say today's date, in second table I have Customers debit transaction detals

       

      custBalanceTable:

      custIdcustBal
      a190000
      a240000

       

      custTrnDetalsTable:

      custIdInv. No.Inv. DateInv. Amount
      a1INV-8010/10/201225000
      a1INV-7509/10/201225000
      a1INV-5005/10/201225000
      a1INV-4704/10/201235000
      a1INV-4403/10/201140000
      a2INV-7910/10/201220000
      a2INV-7809/10/201215000
      a2INV-6506/10/20123000
      a2INV-4503/10/201210000
      a2INV-3015/09/201220000

       

      with the help of these 2 tables I want to create a intermedieate result Table

       

      intermedieateTable:

      custIdInv. No.Inv. DateInv. AmountCust. BalanceCalculationO/s AmtO/sRemark
      abc=( b-a )Days
      a1INV-8010/10/2012250009000065000250002Full Inv O/s
      a1INV-7509/10/2012250006500040000250003Full Inv O/s
      a1INV-5005/10/2012250004000015000250007Full Inv O/s
      a1INV-4704/10/20123500015000-20000150008Partial Inv O/s
      a1INV-4403/10/201140000-20000-6000009
      a2INV-7910/10/2012200004000020000200002Full Inv O/s
      a2INV-7809/10/201215000200005000150003Full Inv O/s
      a2INV-6506/10/201230005000200030006Full Inv O/s
      a2INV-4503/10/2012100002000-800020009Partial Inv O/s
      a2INV-3015/09/201220000-8000-28000027

       

       

      From this table I want to load a Final result table.

       

      FinalTable:

      custIdInv. No.Inv. DateO/s DaysO/s Amount
      a1INV-8010/10/2012225000
      a1INV-7509/10/2012325000
      a1INV-5005/10/2012725000
      a1INV-4704/10/2012815000
      a2INV-7910/10/2012220000
      a2INV-7809/10/2012315000
      a2INV-6506/10/201263000
      a2INV-4503/10/201292000

       

       

      I tryied previous in combanitation with peek and other soloutions given in community under the tag accumulate , running total, cummulative running toal

      but I am fail. I can solve it on VB form or in VBA form but I want to solve it on QlikView Script.

       

      So please help me to solve it.

       

      Thanks in Advance

       

      Regards

       

      - Indian Qv Lover

        • Re: How To Solve This Scenario On Script
          Dave Riley

          Hi indianqvlover,

           

           

          This script should do most of it ..

           

          custBalanceTable:

          LOAD * INLINE [

          custId,    custBal

          a1,    90000

          a2,    40000];

           

          custTrnDetalsTable:

          LOAD * INLINE [

          custId,    InvNo,    InvDate,    InvAmount,

          a1,    INV-80,    10/10/2012,    25000

          a1,    INV-75,    09/10/2012,    25000

          a1,    INV-50,    05/10/2012,    25000

          a1,    INV-47,    04/10/2012,    35000

          a1,    INV-44,    03/10/2012,    40000

          a2,    INV-79,    10/10/2012,    20000

          a2,    INV-78,    09/10/2012,    15000

          a2,    INV-65,    06/10/2012,    3000

          a2,    INV-45,    03/10/2012,    10000

          a2,    INV-30,    15/09/2012,    20000];

           

           

          // For each custID, create running total in reverse chronological order

          For r = 1 to NoOfRows('custBalanceTable')

           

              intermediateTable:

              Load *, nummax(i_custBal,0) - nummax(i_Calculation,0) as [i_O/s Amt], today() - i_InvDate as [i_O/s Days];

              Load

                  custId as i_custId,

                  InvNo as i_InvNo,

                  InvDate as i_InvDate,

                  InvAmount as i_InvAmount,

                  if(custId <> peek('i_custId'), FieldValue('custBal', $(r)), peek('i_Calculation')) as i_custBal,       

                  if(custId <> peek('i_custId'), FieldValue('custBal', $(r)), peek('i_Calculation')) - InvAmount as i_Calculation

              resident custTrnDetalsTable

              where custId = FieldValue('custId', $(r))

              order by InvDate desc;

           

          Next r;

           

           

          flipside

            • Re: How To Solve This Scenario On Script

              hi Flipside,

               

              I am trying your solution. But I am in doubt about the performance because there is more then 1700000 records in details for mor then 8000 customers

               

              Regards

               

              -IndianQvLover

                • Re: How To Solve This Scenario On Script

                  hi IndianQvLover,

                   

                  Solution provided by Flipside is correct as far as your doubt about performance on the larg data set is concern.

                   

                  Answare of your doubt is comming from the your Intermediate Table. your can use 2 nested For-Next loops in Flipside's solutions and when your calculation column value is less then Zero then you can exit from inner For-Next loop

                   

                  Hope it will help.

                   

                  Regards

                   

                  - Anil

                • Re: How To Solve This Scenario On Script

                  Sir,

                   

                  Solution given by you is working fine and it the same as I want but I am confuse, well after decleartion of intermediateTable you are loading *, and some other columns but from which table they are coming your are using 2 time load in the intermediateTable, Here I am confuse

                   

                  Can you please explain this.

                   

                    intermediateTable:

                      Load *, nummax(i_custBal,0) - nummax(i_Calculation,0) as [i_O/s Amt], today() - i_InvDate as [i_O/s Days];

                      Load

                          custId as i_custId,

                          InvNo as i_InvNo,

                   

                  Regards

                   

                  - IndianQvLover

                    • Re: How To Solve This Scenario On Script
                      Dave Riley

                      Hi,

                       

                      The first LOAD statement pulls data from the second LOAD statement (a bit like a sub query) - this just makes it easier to reference the calculated fields i_custBal and i_Calculation and use them in further calculated fields.  I included the * as all the fields in the sub query need loading into the table.

                       

                      flipside

                      • Re: How To Solve This Scenario On Script

                        hi IndianQvlover,

                         

                        I am trying to explain Flipside's solution's tips & tricks step by step

                         

                        1. Nested Load - If you are from RDBMS programming background then you can understand sub query / nested query like

                         

                        select *, (calc1 * calc2) as calc4

                             select *, (field4+field6) as calc1,(field5-field7) as calc2, ( (field8*field9)/field10 ) as calc3 from (

                                  select field1, field2, field3 ............field10 from table1

                             ) as tab1

                        ) as tab2

                         

                        The Query returning total 14 columns - 10 column from inner most query, 3 column added on middle query, 1 column added on outer most query

                         

                        Here Flipside is working on same approach

                         

                        2. Peek - Syntax of peek is (FieldName, RowNo, Table Name) but when using without 2 other optional parameter then return / give value from previously read record.

                         

                        3. nummax :: rangemax - Will give maximum value from the bunch of values rangemax(40,30,60,100) will return - 100, always use rangesum insted of nummax because nummax is obsolete now.

                         

                        Flipside given a smart solution.

                         

                        Hope my explanations will help if I explain properly.

                         

                        Regards

                         

                        - Anil