topic Complex sql calculation in qlikview scripting!!! in QlikView

Complex sql calculation in qlikview scripting!!!

qliklearnervir — Fri, 09 Jun 2017 05:03:07 GMT

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 — Fri, 09 Jun 2017 05:30:10 GMT

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

qliklearnervir — Fri, 09 Jun 2017 05:46:14 GMT

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 — Fri, 09 Jun 2017 05:52:39 GMT

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

jonathandienst — Fri, 09 Jun 2017 06:03:29 GMT

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;

Re: Complex sql calculation in qlikview scripting!!!

jonathandienst — Fri, 09 Jun 2017 06:05:22 GMT

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;