Error in script

[naziralala]

My script is giving an incorrect number in case of bookings only.

Here is my script...

Let vCurrYear=Year(Today());

[Sency]:

LOAD

    [B Number] ,

    [DepYear] as [Travel Year],

  [SupplierID],

Description,

  Price,

Duration,

Count

Resident [BItem];

left join

Load

SupplierID,

[H Name] as [HCName]

Resident [HView];

left Join

LOAD

[B Number] ,

[Agency Code],

BookingDate,

GFlag,

[BCounter]

Resident [BHeader];

left Join

LOAD

[Agency Code] ,

[AgencyNme]

  Resident Agency;

//For 2014 and For 2015

[Output Agency(2014 and 2015)]:

LOAD  

[Agency Code],

[AgencyNme],

[HCName],

[B Number]&'-'& [Agency Code]&'-'&[AgencyNme]&'-'& [HCName] as Key,

  sum(Price) as [CYH Rev],

        sum( [BCounter]) as [CYBookings],

  sum(Duration*Count)  as [CYR N]

Resident [Sency]

  WHERE [Travel Year] = $(vCurrYear) 

  and GFlag='Y'

  and index([ Agency Code],'N00')=0

  and [Description]='Hotel'

  Group By [ Agency Code],[AgencyNme], [HCName];

Let vCurrYear=($(vCurrYear)-1);

Join ([Output Agency(2014 and 2015)])

Load

LOAD  

[Agency Code],

[AgencyNme],

[HCName],

[Agency Code]&'-'&[AgencyNme]&'-'& [HCName] as Key,

  sum(Price) as [PYH Rev],

        sum( [BCounter]) as [PYBookings],

  sum(Duration*Count)  as [PYR N]

Resident [Sency]

  WHERE [Travel Year] = $(vCurrYear) 

  and GFlag='Y'

  and index([ Agency Code],'N00')=0

  and [Description]='Hotel'

and BookingDate<='11/29/2014'

  Group By [ Agency Code],[AgencyNme], [HCName];

Drop field Key from [Output Agency(2014 and 2015)];

Store  [Agency Code],

  [AgencyName] as [Agency Name],

[HCName] as [Hotel],

[CYH Rev],

  [CYBookings],

[CYR N],

[PYH Rev],

[PYBookings],

[PYR N]

From [Output Agency(2014 and 2015)]

INTO [$(vOutfile)](txt);

Drop Table [Sency];

Drop Table [Output Agency(2014 and 2015)];

My problem in the output is that the bookings are not recorded correct, but the revenue and Duration count is correct.

Please assist.

Thanks in advance.

[avinashelite]

I feel the problem is with the joins your doing ...check the join conditions one by one you will get the error

[raghvendrasingh]

Hi naziralala,

Please take all the columns (except measures) in group by clause.You missed [B Number] column in group by, please take this in group by and run the script:-

[Output Agency(2014 and 2015)]:

LOAD

[Agency Code],

[AgencyNme],

[HCName],

[B Number]&'-'& [Agency Code]&'-'&[AgencyNme]&'-'& [HCName] as Key,

  sum(Price) as [CYH Rev],

        sum( [BCounter]) as [CYBookings],

  sum(Duration*Count)  as [CYR N]

Resident [Sency]

  WHERE [Travel Year] = $(vCurrYear)

  and GFlag='Y'

  and index([ Agency Code],'N00')=0

  and [Description]='Hotel'

  Group By [B Number],[ Agency Code],[AgencyNme], [HCName];

Regards,

Raghvendra

[naziralala]

Yes, you are right..

Can you guide me.. I am getting more numbers only for bookings.

[naziralala]

Hi All,

The problem is in the Join.

Inspite of using where exists function, it is not helping still.

Can anyone assist..

[naziralala]

Hey Avinash,

I used the where exists but still i am not getting the correct count when joining.

Thanks..