7 Replies Latest reply: Jan 6, 2013 4:05 AM by hagay hadad RSS

    load problem

    hagay hadad

      hi,

      i have a problem to load this tables,

       

      ALLcoted_price:
      LOAD 
         `LineRz_LI_RzLnAlc` as "מספר שורת הזמנה", 
         `Hotel_LI_RzLnAlc` as "מלון שהייה",
          date (`Date_RzLnAlc`,'DD-MM-YYYY') as "תאריך שהייה לעלות";
      SQL SELECT *
      FROM `Tbl_LineRz_Allocation`
      where year(`Date_RzLnAlc`)>2011;
      Join (ALLcoted_price)
      LOAD `Index_LRz`as "מספר שורת הזמנה",
      //    if (`Tourist_Rez`=0,'ישראלי','תייר') as  Tourist_Rez1  ,
          (`To_LRz` - `From_LRz`) as Rez_RoomNights,
      //      `To_LRz` as CheckOut_Rez,
      //    `From_LRz` as CheckIn_Rez,
      //       `Hotel_Link_Index_Rez`,// אינדקס מלון, 
             `RoomType_CTG_LRz` as Index_RoomType, // אינדקס סוג חדר
            `Type_LRz` as Index_RoomSet;// אינדקס תפוסה,
                   
      
      SQL SELECT `Tbl_LineRz`.*
      FROM `Tbl_LineRz` 
      where year(`To_LRz`)>2011;
      //Store ALLcoted_price into $(myBILoad4);
      //drop table ALLcoted_price;
      CostPrice:
      LOAD CostPrice_hotel, 
           CostPrice_IndexRoomType, 
           CostPrice_IndexPeople, 
           CostPrice_ChackIn, 
           CostPrice_ChackOut, 
           CostPrice_Cur, 
           CostPrice_cost, 
           CostPrice_IfRoom, 
           CostPrice_ifPeople
      FROM $(costnew); //(ooxml, embedded labels, table is [costPrice]) ;
      join(CostPrice)
      LOAD `Index_LRz` as "מספר שורת הזמנה",
      //    if (`Tourist_Rez`=0,'ישראלי','תייר') as  Tourist_Rez1  ,
      //   (`To_LRz` - `From_LRz`) as Rez_RoomNights,
      //      `To_LRz` as CheckOut_Rez,
      //    `From_LRz` as CheckIn_Rez,
      //       `Hotel_Link_Index_Rez`,// אינדקס מלון, 
           `RoomType_CTG_LRz` as Index_RoomType, // אינדקס סוג חדר
          `Type_LRz` as Index_RoomSet,// אינדקס תפוסה,
      //   `LineRz_LI_RzLnAlc` as "מספר שורת הזמנה", 
         `Hotel_LI_RzLnAlc` as "מלון שהייה",
          date (`Date_RzLnAlc`,'DD-MM-YYYY') as "תאריך שהייה לעלות" 
      // if (date (`Date_RzLnAlc`,'DD-MM-YYYY')>
      
      resident ALLcoted_price
      where ($(costnew).CostPrice_hotel=ALLcoted_price.Hotel_LI_RzLnAlc) and (ALLcoted_price.Hotel_LI_RzLnAlc=$(costnew).CostPrice_IndexPeople) and (ALLcoted_price.RoomType_CTG_LRz=$(costnew).CostPrice_IndexRoomType) 
      and ((ALLcoted_price.Date_RzLnAlc)>=$(costnew).CostPrice_ChackIn) and ((ALLcoted_price.Date_RzLnAlc)<=$(costnew).CostPrice_ChackOut);
      
      
      

       

      when i try to RELAOD i get a Eror :

       

      Eror1.jpg

       

      if someone can help me...

        • Re: load problem
          Jonathan Brough

          What is the value of the costnew variable.

          Maybe

            FROM '$(costnew)';

          instead of
            FROM $(costnew);

          Jonathan

          • Re: load problem

            Hello,

             

            You must erase the variable $(costnew) after your "where" condition. You don't need this.

             

            So, you will have :

             

            [...]

            resident ALLcoted_price
            where (CostPrice_hotel=ALLcoted_price.Hotel_LI_RzLnAlc)

            and (ALLcoted_price.Hotel_LI_RzLnAlc=CostPrice_IndexPeople)

            and (ALLcoted_price.RoomType_CTG_LRz=CostPrice_IndexRoomType)
            and ((ALLcoted_price.Date_RzLnAlc)>=CostPrice_ChackIn)

            and ((ALLcoted_price.Date_RzLnAlc)<=CostPrice_ChackOut);

             

             

            Amand Dupretz

              • Re: load problem
                hagay hadad

                thank you for your answer,

                but it's steal don't work...

                  • Re: load problem
                    Tanel Rüütli

                    There are several mistakes in the "resident ALLcoted_price" part
                    1) you are using field names that don't exist in ALLcoted_price table (because you renamed those fields in the first "ALLcoted_price:" load statement). Make sure that the field names are correct.
                    2) the "where" condition looks like SQL syntax, this is not how join works in QV script. QV makes a join based on common field names in source and target table. Where-condition can refer only to field names of source table but not target table. For example, ALLcoted_price.Hotel_LI_RzLnAlc=CostPrice_IndexPeople won't work because CostPrice_IndexPeople exists in target table only. This kind of "equation" (join) is made automatically if field names in both tables are the same.
                    3) comparisons between source and target table fields (like ALLcoted_price.Date_RzLnAlc>=CostPrice_ChackIn) only work in SQL, not in QV. For this kind of comparison (in order to remove unwnted records) you can make another inner join after the Resident load, something like this:

                     

                    ...

                    join(CostPrice)
                    LOAD
                    Hotel_LI_RzLnAlc as CostPrice_hotel,
                    Index_RoomType as CostPrice_IndexRoomType,
                    Index_RoomSet,
                    Date_RzLnAlc
                    resident ALLcoted_price // no where conditions
                    ;
                    inner join (CostPrice)
                    load *
                    resident CostPrice
                    where Date_RzLnAlc>=CostPrice_ChackIn
                    and Date_RzLnAlc<=CostPrice_ChackOut
                    ;