Complex sql calculation in qlikview scripting!!!

[qliklearnervir]

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

[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;

[qliklearnervir]

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....

[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;

[jonathandienst]

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;

[jonathandienst]

If TotalBookingPrice is replicated multiple times is the same for all instances of a given Booking_wid, then change the last join to

JOIN (Booking)

LOAD

  Booking_wid

  Only(TotalBookingPrice) + SumRPayment - SumDPayment as Outstanding

Resident Booking

Group By Booking_wid;