load problem

[hagay]

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 :

if someone can help me...

[Anonymous]

What is the value of the costnew variable.

Maybe

  FROM '$(costnew)';

instead of
  FROM $(costnew);

Jonathan

[#3]

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

[hagay]

thank you for your answer,

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

[hagay]

the value is link to excel file.

[Anonymous]

I see that now and think Amand's response is more relevant than mine was.

Jonathan

[tanelry · Accepted solution]

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
;

[hagay]

thank you very much !!

you help me to understand QV better .