5 Replies Latest reply: Jun 9, 2017 2:05 AM by Jonathan Dienst RSS

    Complex sql calculation in qlikview scripting!!!

    vir vir

      I have a table given below :

      Booking:

      Booking_wid,

      Payment_Value,

      Payment_type_source_value,

      TotalBookingPrice

      FROM $(QVDLocation)Booking.qvd;

       

      I want below out come as outstanding ::

      Booking.TotalBookingPrice – (Sum(Payment_Value) WHERE Payment_type_source_value in (‘RB’,’RD’)

      Sum(Sum(Payment_Value) WHERE Payment_type_source_value in (‘CB,CD)) for same Booking_wid AS Outstanding;

       

       

      I have to perform this calculation in qlikview script only as data volume is very huge and i don't want to use chart.....

       

      please suggest

        • Re: Complex sql calculation in qlikview scripting!!!
          Anil Babu Samineni

          May be this?

           

          Booking:

          Load Booking_wid,

          Payment_Value,

          Payment_type_source_value,

          TotalBookingPrice

          FROM $(QVDLocation)Booking.qvd;

           

          Left Join (Booking)
          Load *, Sum(Payment_Value) as Booking.TotalBookingPrice Resident Booking Where Match(Payment_type_source_value, 'RB','RD') Group By Booking_wid, Payment_Value, Payment_type_source_value, TotalBookingPrice;


          Left Join (Booking)

          Load *, Sum(Payment_Value) as OutStadning Resident Booking Where Match(Payment_type_source_value, 'CB', 'CD') Group By Booking_wid;

            • Re: Complex sql calculation in qlikview scripting!!!
              vir vir

              no this will not work..

               

               

              we need to divide the query in 2part.

               

              1st part sum(payment value) where payment type source value =''RB and RD - (subtract) SUm(Payment value) where payment type source value =CB and CD groupy by booking wid

               

               

              finally in 2nd part

               

              we need to subtract 1st part with total booking price.....to calculate uptstanding....

                • Re: Complex sql calculation in qlikview scripting!!!
                  Anil Babu Samineni

                  I am not sure about 2nd one, But 1st one you may need like below

                   

                  Booking:

                  Load Booking_wid,

                  Payment_Value,

                  Payment_type_source_value,

                  TotalBookingPrice

                  FROM $(QVDLocation)Booking.qvd;

                   

                  Left Join
                  Load *, If(Payment_type_source_value='RB' or Payment_type_source_value = 'RD', Sum(Payment_Value))-If(Payment_type_source_value='CB' or Payment_type_source_value = 'CD', Sum(Payment_Value)) as Booking.TotalBookingPrice Resident Booking Group By Booking_wid, Payment_Value, Payment_type_source_value, TotalBookingPrice;

              • Re: Complex sql calculation in qlikview scripting!!!
                Jonathan Dienst

                This will be difficult to do as you have mixed granularity in your data and expression. Is Booking_wid a unique key in Booking? If it is, then you can only have single payment value for each Booking_wid. If not, then you have multiple entries for TotalBookingPrice; and you do not have a key for joining.

                 

                You could do something like this:

                 

                Booking:

                LOAD

                  Booking_wid,

                  Payment_Value,

                  Payment_type_source_value,

                  TotalBookingPrice

                FROM $(QVDLocation)Booking.qvd;

                 

                JOIN (Booking)

                LOAD Booking_wid,

                  Sum(If(Match(Payment_type_source_value, 'RB', 'RD'), Payment_Value)) as SumRPayment,

                  Sum(If(Match(Payment_type_source_value, 'CB', 'CD'), Payment_Value)) as SumDPayment

                Resident Booking

                Group By Booking_wid;

                 

                JOIN (Booking)

                LOAD

                  Booking_wid

                  Sum(TotalBookingPrice) + SumRPayment - SumDPayment as Outstanding

                Resident Booking

                Group By Booking_wid;

                 

                DROP Fields SumRPayment, SumDPayment;