topic Re: Resident left join problem in QlikView

Resident left join problem

siva_boggarapu — Mon, 23 Jul 2012 05:33:09 GMT

Hi There,

I am trying to load the script below. But, while loading the data, I am getting error invalid column. Can anyone hlep me what is the wrong in my script?

morpheus:

LOAD Year,

[contract name],

[contract name year],

[RuleGroup Name],

Carrier,

[service class name],

ClassCode,

SellupAmount,

Fare,

Season ,

City_from,

City_to,

From_region,

To_region,

farecomp_class,

contractname

FROM

mopheusdata.qvd

(qvd);

left Join(morpheus)

LOAD TourCode,

Season,

DomGtwy as City_from ,

[Int Gate] as City_to ,

OutboCost,

RetCost,

InternalCost,

WkndCost,

DateFrom,

DateTo,

Region,

BudgetYear

FROM

Budget_1.xls

(biff, embedded labels, table is [ETUS Budget$]);

Overunder:

left join(morpheus)

LOAD OU_Airline,

OU_HKMinusCax,

OU_ProductMarketCode,

OU_IsAdHoc,

OU_IsRT,

OU_DomGate as City_from,

OU_IntlGate as City_to,

OU_Season as Season,

OU_EstimatedCost,

YEAR

FROM

OverUnder12.qvd

(qvd);

left join (morpheus)

load

OU_DomGate as City_from,

OU_IntlGate as City_to,

OU_Season as Season,

sum(OU_HKMinusCax) as OW_PAX

resident Overunder

group by OU_Season,OU_DomGate,OU_IntlGate;

left join (morpheus)

load

OU_DomGate as City_from,

OU_IntlGate as City_to,

OU_Season as Season,

sum(OU_HKMinusCax) as CarrierPax

resident Overunder

Where OU_Airline='LH'

group by OU_Season,OU_DomGate,OU_IntlGate;

Left join(morpheus)

load

OU_DomGate as City_from,

OU_IntlGate as City_to,

OU_Season as Season,

min(OU_EstimatedCost) as IsAdhoc

resident Overunder

Where OU_IsAdHoc<>0

group by OU_Season,OU_DomGate,OU_IntlGate;

Thanks,

Siva

Re: Resident left join problem

jonathandienst — Mon, 23 Jul 2012 06:43:02 GMT

Siva

In a quick scan I see two issues - I don't know i these are causing your problem

In the first load, you load the field Year. Later on you use YEAR. QV field names are case sensitive, so these two are not the same field. Was this your intent?
The script never creates Overunder as far as I can see, because the load after the label is a left join.

I suggest that you go to the debugger and single step through the script (you can also limit the number of rows in the debugger), and see exaclty which of your load statements in causing your error.

Hope that helps

Jonathan

Re: Resident left join problem

Mon, 23 Jul 2012 12:26:54 GMT

by the way

check

Year and YEAR

and

contract name and contractname

Re: Resident left join problem

Mon, 23 Jul 2012 12:33:07 GMT

try this:

morpheus:

LOAD Year,

[contract name],

[contract name year],

[RuleGroup Name],

Carrier,

[service class name],

ClassCode,

SellupAmount,

Fare,

Season ,

City_from,

City_to,

From_region,

To_region,

farecomp_class,

contractname

FROM

mopheusdata.qvd

(qvd);

left Join(morpheus)

LOAD TourCode,

Season,

DomGtwy as City_from ,

[Int Gate] as City_to ,

OutboCost,

RetCost,

InternalCost,

WkndCost,

DateFrom,

DateTo,

Region,

BudgetYear

FROM

Budget_1.xls

(biff, embedded labels, table is [ETUS Budget$]);

Overunder:

left join(morpheus)

LOAD OU_Airline,

OU_HKMinusCax,

OU_ProductMarketCode,

OU_IsAdHoc,

OU_IsRT,

OU_DomGate as City_from,

OU_IntlGate as City_to,

OU_Season as Season,

OU_EstimatedCost,

YEAR as Year

FROM

OverUnder12.qvd

(qvd);

left join (morpheus)

load

City_from,

City_to,

Season,

sum(OU_HKMinusCax) as OW_PAX

resident Overunder

group by OU_Season,OU_DomGate,OU_IntlGate;

left join (morpheus)

load

City_from,

City_to,

Season,

sum(OU_HKMinusCax) as CarrierPax

resident Overunder

Where OU_Airline='LH'

group by OU_Season,OU_DomGate,OU_IntlGate;

Left join(morpheus)

load

City_from,

City_to,

Season,

min(OU_EstimatedCost) as IsAdhoc

resident Overunder

Where OU_IsAdHoc<>0

group by OU_Season,OU_DomGate,OU_IntlGate;