Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Where clause with qualified fields is not working

 

I am getting the following error:  Error: Field not found - <Date>

 

QUALIFY *;
  GeoLocColSum:
 
LOAD REGION
,
     Floor(DATE) as Date
,
  "TOTAL_REQUESTS"
,
  "PASSED_REQUESTS"
,
  "FAILED_REQUESTS"
,
  ISMAXDATE
 
 
Resident GeoLocColSumTemp Where Date>$(vMaxDate)
;

It is showing the same error even if I use Where GeoLocColSum.Date>$(vMaxDate);

 

I think this is because of Qualify.

 

Your help is appreciated.

 

1 Solution

Accepted Solutions
MVP
MVP

Re: Where clause with qualified fields is not working

Try

LET vMaxDate=Peek('MaxDate', 0, 'MaxDate');

and maybe add a num() around the Peek():

LET vMaxDate = Num(Peek('MaxDate', 0, 'MaxDate'));

13 Replies

Re: Where clause with qualified fields is not working

Your DateField is DATE and not Date. Try with DATE (QlikView is Case Sensitive)

QUALIFY *;
  GeoLocColSum:
 
LOAD REGION
,
    Floor(DATE) as Date
,
  "TOTAL_REQUESTS"
,
  "PASSED_REQUESTS"
,
  "FAILED_REQUESTS"
,
  ISMAXDATE
 
 
Resident GeoLocColSumTemp

Where DATE>$(vMaxDate);

Not applicable

Re: Where clause with qualified fields is not working

Hi Sunny,

I have used the following format. But still I am getting same error:

Where DATE>$(vMaxDate);

Re: Where clause with qualified fields is not working

What error did you get this time?

Not applicable

Re: Where clause with qualified fields is not working

Resident GeoLocColSumTemp Where DATE>

Error: Field not found - <>>

Re: Where clause with qualified fields is not working

Would you be able to paste a screenshot of the error you are seeing?

Not applicable

Re: Where clause with qualified fields is not working

Hi, Following is the detailed log:

5/3/2016 8:33:02 AM: 0233  MaxDate:

5/3/2016 8:33:02 AM: 0234  Load Max(GeoLocColSum.Date) as MaxDate

5/3/2016 8:33:02 AM: 0235  From ..\QVDs\GeoLocColSum.qvd(qvd)

5/3/2016 8:33:02 AM:        1 fields found: MaxDate, 1 lines fetched

5/3/2016 8:33:02 AM: 0237  LET vMaxDate=Peek(MaxDate, 0, MaxDate)

5/3/2016 8:33:02 AM: 0239  Drop Table MaxDate

5/3/2016 8:33:02 AM: 0241  QUALIFY *

5/3/2016 8:33:02 AM: 0244  GeoLocColSum:

5/3/2016 8:33:02 AM: 0245  LOAD REGION,

5/3/2016 8:33:02 AM: 0246  

5/3/2016 8:33:02 AM: 0247     Floor(DATE) as Date,

5/3/2016 8:33:02 AM: 0248     ' ' as Flag,

5/3/2016 8:33:02 AM: 0249      "TOTAL_REQUESTS",

5/3/2016 8:33:02 AM: 0250      "PASSED_REQUESTS",

5/3/2016 8:33:02 AM: 0251      "FAILED_REQUESTS",

5/3/2016 8:33:02 AM: 0252      "FAILED_REQUESTS_SYNC",

5/3/2016 8:33:02 AM: 0253      "AVERAGE_TOTAL_PROCESS_TIME",

5/3/2016 8:33:02 AM: 0254      "AVERAGE_TOTAL_PROC_TIME_SYNC",

5/3/2016 8:33:02 AM: 0255      "AVERAGE_DATA_SIZE_RECEIVED",

5/3/2016 8:33:02 AM: 0256      "AVG_DATA_SIZE_RECEIVED_SYNC",

5/3/2016 8:33:02 AM: 0257      "REQUEST_TYPE_3",

5/3/2016 8:33:02 AM: 0258      "REQUEST_TYPE_4",

5/3/2016 8:33:02 AM: 0259      "REQUEST_TYPE_7",

5/3/2016 8:33:02 AM: 0260      ISMAXDATE

5/3/2016 8:33:02 AM: 0261      

5/3/2016 8:33:02 AM: 0262  Resident GeoLocColSumTemp Where DATE>

5/3/2016 8:33:02 AM:       Error: Field not found - <>>

5/3/2016 8:33:02 AM:       General Script Error

5/3/2016 8:33:02 AM:       Execution Failed

5/3/2016 8:33:02 AM:      Execution finished.

Re: Where clause with qualified fields is not working

I think the issue is your variable. Can you add a Trace statement to check your variable after you create it?

LET vMaxDate=Peek(MaxDate, 0, MaxDate);

TRACE $(vMaxDate);

EXIT SCRIPT;

Not applicable

Re: Where clause with qualified fields is not working

I have tried this and following is the log:

MaxDate:
5/3/2016 9:25:22 AM: 0234  Load Max(GeoLocColSum.Date) as MaxDate
5/3/2016 9:25:22 AM: 0235  From ..\QVDs\GeoLocColSum.qvd(qvd)
5/3/2016 9:25:22 AM:        1 fields found: MaxDate, 1 lines fetched
5/3/2016 9:25:22 AM: 0237  LET vMaxDate=Peek(MaxDate, 0, MaxDate)
5/3/2016 9:25:22 AM: 0239  TRACE
5/3/2016 9:25:22 AM: 0239 

5/3/2016 9:25:22 AM: 0240  EXIT SCRIPT
5/3/2016 9:25:28 AM:      Execution finished.

MVP
MVP

Re: Where clause with qualified fields is not working

Try

LET vMaxDate=Peek('MaxDate', 0, 'MaxDate');

and maybe add a num() around the Peek():

LET vMaxDate = Num(Peek('MaxDate', 0, 'MaxDate'));

Community Browser