3 Replies Latest reply: Jul 23, 2012 8:33 AM by Wojciech Parzyszek RSS

    Resident left  join problem

    Siva Boggarapu

      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
          Jonathan Dienst

          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

            by the way

            check

             

            Year and YEAR

            and

             

            contract name and contractname

            • Re: Resident left  join problem

              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;