topic Re: Error in script in QlikView

Error in script

naziralala — Tue, 08 Dec 2015 03:28:59 GMT

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.

Re: Error in script

avinashelite — Tue, 08 Dec 2015 05:28:24 GMT

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

Re: Error in script

raghvendrasingh — Tue, 08 Dec 2015 06:22:49 GMT

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

Re: Error in script

naziralala — Tue, 08 Dec 2015 18:55:50 GMT

Yes, you are right..

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

Re: Error in script

naziralala — Tue, 08 Dec 2015 21:40:40 GMT

Hi All,

The problem is in the Join.

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

Can anyone assist..

Re: Error in script

naziralala — Wed, 09 Dec 2015 04:14:09 GMT

Hey Avinash,

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

Thanks..