topic Re: load problem in QlikView

load problem

hagay — Thu, 03 Jan 2013 14:02:46 GMT

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...

Re: load problem

Anonymous — Thu, 03 Jan 2013 14:14:10 GMT

What is the value of the costnew variable.

Maybe

FROM '$(costnew)';

instead of
FROM $(costnew);

Jonathan

Re: load problem

Thu, 03 Jan 2013 14:21:00 GMT

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 — Thu, 03 Jan 2013 21:21:41 GMT

thank you for your answer,

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

Re: load problem

hagay — Thu, 03 Jan 2013 21:22:23 GMT

the value is link to excel file.

Re: load problem

Anonymous — Fri, 04 Jan 2013 07:53:52 GMT

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

Jonathan

Re: load problem

tanelry — Fri, 04 Jan 2013 11:36:33 GMT

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
;

Re: load problem

hagay — Sun, 06 Jan 2013 09:05:11 GMT

thank you very much !!

you help me to understand QV better .