calculate the output

[Kohli]

I have table like

Table1:

Flight .       seat1 .   seat2         Fair                 Days   

1                 0 .         60             1500 .               30 .         

1                 61        120 .          1700                 30           

1 .              121        180           2000                 30            

2                 0 .          30            1000 .                7

2 .               31          60 .          1200                  7

2                 61          90            1300                  7

3 .               0           15             1000                  1

3                 16          30             2000                 1

3                 31          60             2500                 1

Table2: 

Flight        Quantity

1                    80

2                    65

3                    35

How can i get the total revenue for that?

i want output like

Flght1    =  (60*1500*30) + (20*1700*30) .                         (80= 60+20)

Flight2 .  = (30*1000*7) + (30*1200*7) + (5*1300*7)          (65= 30+30+5)

[vunguyenq89]

I assume seat1 should start from 1 instead of 0, so that first row in Table1 is 60 seats and not 61 seats for example?

If so, check the attached qvw document and see if it matches your requirements. The general idea is to calculate revenue for each row in Table1 based on Quantity in Table2

You can also drop fields NoOfSeat and SeatRevenue if you don't want them.

[Kohli]

Yes, What are you saying is 100% correct. But how can i segregate the Quantity related to seats. Can you please share me the Script.

[vunguyenq89]

I already attached a sample qvw document in my previous comment, you can find the script there.

Anyway, the script is as follows:

Mapping_Table2:
MAPPING LOAD * INLINE [
    Flight, Quantity
    1, 80
    2, 65
    3, 35
];

Table1:
LOAD *,
NoOfSeat * Fair * Days as SeatRevenue;
LOAD *,
If(SeatQuantity > seat1,If(SeatQuantity < seat2, SeatQuantity - seat1 + 1, seat2-seat1+1),0) as NoOfSeat;
LOAD *,
ApplyMap('Mapping_Table2',Flight,0) as SeatQuantity;
LOAD * INLINE [
    Flight, seat1, seat2, Fair, Days
    1, 1, 60, 1500, 30
    1, 61, 120, 1700, 30
    1, 121, 180, 2000, 30
    2, 1, 30, 1000, 7
    2, 31, 60, 1200, 7
    2, 61, 90, 1300, 7
    3, 1, 15, 1000, 1
    3, 16, 30, 2000, 1
    3, 31, 60, 2500, 1
];

Table2:
LOAD
Flight,
Max(SeatQuantity) as Quantity,
Sum(SeatRevenue) as Revenue
Resident Table1
Group By Flight;

DROP FIELD SeatQuantity;

[Kohli]

Thank you Nguen. Its perfectly working.....