Script Qlikview

Question

I want to use a field, present in the primary load, but in another load.

For example:

[Table 1]:
Select 
  Field1, 
  Field2
From 
  Database1
;

[Table 2]:
LOAD:
  if Field1(from Table 1) > 0 then 1 as Filter
Select 
  * 
From 
  Database2
;

I'm looking for the best way to use the Field1 (from Table 1) in the Table 2 in Qlikview. But it tells me that Field1 is not present in the database.

Answer 1

There are few options but in general its depends on your data:

• bring Field1 into Table 2. If there is something common between Table 1 and Table 2 then you can to join both tables (or subset of them) and then Field1 will be available in the resulting table. (This depends on your data but for me this is usually the preferred way)

• create variable(s) that holds specific Field1 value. For example:

  [Table 1]:
  Select 
    Field1, 
    Field2
  From 
    Database1
  ;
  
  set vField1 = peek('Field1');
  
  [Table 2]:
  LOAD:
    if ($(vField1) > 0, 1, 0) as Filter
  Select 
    * 
  From 
    Database2
  ;
  

  In the script above peek() function will return the last loaded value from Field1 and set the value into vField1 variable, which is then used into Table 2 load. Bear in mind that peek() returns only one value!

• Lookup() function can look for a specific value in [Table 1].Field1 based or not on the current field value from Table 2.

  ProductList:
    Load * Inline [
  ProductID|Product|Category|Price
  1        |AA     |1       |1
  2        |BB     |1       |3
  3        |CC     |2       |8
  4        |DD     |3       |2
  ] (delimiter is '|');
  
  OrderData:
    Load 
      *, 
      Lookup('Category', 'Product', ProductID, 'ProductList') as CategoryID
  Inline [
  InvoiceID|CustomerID|ProductID|Units
  1        |Astrida   |1        |8
  1        |Astrida   |2        |6
  2        |Betacab   |3        |10
  3        |Divadip   |3        |5
  4        |Divadip   |4        |10
  ] (delimiter is '|');
  
  

  In the example above Lookup('Category', 'Product', ProductID, 'ProductList') will look into ProductList table and into Category field and will return Product value based on the currently loaded ProductID value. So if there is a common values between ProductID and Category the result field CategoryID will contains data from the first table.