topic Re: Field not found error with preceding load in QlikView

Field not found error with preceding load

qlikviewwizard — Thu, 02 Apr 2015 03:13:02 GMT

Will this script work as [Closing Amount] is not listed Account Balance Load?

SET BalanceSheetRange = 3000; // Balance Sheet Data (up to Account Number 2999) //standard is 4000

TrialBalance:

LOAD

[Company Number] & '_' & [Account Number] as AccountKey,

[Company Number],

[Account Number],

Date(Makedate(Year, Month), 'MMM-YYYY') as MonthYear,

[Balance Forwarded],

Actual,

if([Account Number] < $(BalanceSheetRange), 1, 0) as BS_Flag,

if([Account Number] < $(BalanceSheetRange), 0, 1) as PL_Flag,

if(RowNo() = 1 or [Company Number] <> peek([Company Number]) or [Account Number] <> peek([Account Number]) or Year <> peek(Year), if([Account Number] < $(BalanceSheetRange), if(isnull([Balance Forwarded]), 0, [Balance Forwarded]), 0), peek([Closing Amount])) as [Opening Amount],

;

LOAD

[Company Number],

[Account Number],

Year,

Month,

[Balance Forwarded],

Actual

FROM [General Ledger.xls] (biff, embedded labels, table is [Account Balance$])

WHERE Makedate(Year, Month) <= $(vTodaysDate);

But my script below listed is not working. Throwing Field not found - <DEPTNO> error.

SET DEPTNO = 20; 
EMP:
load 
     EMPNO &'-'& ENAME as Key,
     EMPNO, 
     ENAME,
     Date((HIREDATE), 'MMM-YYYY') as MonthYear,
     DEPTNO,
    if([DEPTNO] < $(DEPTNO), 1, 0) as  BS_Flag,
  if([DEPTNO] < $(DEPTNO), 0, 1) as  PL_Flag;


LOAD EMPNO, 
     ENAME, 
     JOB, 
     MGR, 
     HIREDATE, 
     StartDate, 
     EndDate, 
     PROMOTIONDATE, 
     SAL, 
     COMM, 
    // DEPTNO, 
     DESIGNATION, 
     CHANGE, 
     YEAR, 
     MONTH
FROM
[Documents\Emp.xlsx]
(ooxml, embedded labels, table is Sheet1);

Please help.

Re: Field not found error with preceding load

jagan — Thu, 02 Apr 2015 03:26:41 GMT

Hi,

You have commented the DEPTNO field in line 23, remove that comments and your script works as expected.

Regards,

Jagan.

Re: Field not found error with preceding load

its_anandrjs — Thu, 02 Apr 2015 03:44:31 GMT

Hi,

You can try this ways also

tmpEMP:

LOAD EMPNO,

ENAME,

JOB,

MGR,

HIREDATE,

StartDate,

EndDate,

PROMOTIONDATE,

SAL,

COMM,

DEPTNO,

DESIGNATION,

CHANGE,

YEAR,

MONTH

FROM

[Documents\Emp.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

EMP:

LOAD *,

EMPNO &'-'& ENAME as Key,

EMPNO,

ENAME,

Date((HIREDATE), 'MMM-YYYY') as MonthYear,

DEPTNO,

if([DEPTNO] < $(DEPTNO), 1, 0) as BS_Flag,

if([DEPTNO] < $(DEPTNO), 0, 1) as PL_Flag

Resident tmpEMP;

DROP Table tmpEMP;

Regards

Anand

Re: Field not found error with preceding load

qlikviewwizard — Thu, 02 Apr 2015 04:31:35 GMT

Hi Jagan and Anand,

I agree with you.

But my doubt is there is no [Closing Amount]) in below script. Why it is working?

DEPTNO existed mail load and preceding load. Then only my script is working.

Please help.

LOAD
  [Company Number],
  [Account Number],
  Year,
  Month,
  [Balance Forwarded],
  Actual
FROM [General Ledger.xls] (biff, embedded labels, table is [Account Balance$])
WHERE Makedate(Year, Month) <= $(vTodaysDate);

Re: Field not found error with preceding load

Anonymous — Thu, 02 Apr 2015 05:23:04 GMT

does your file [General Ledger.xls] contains Closing Amount ?

Re: Field not found error with preceding load

jagan — Thu, 02 Apr 2015 05:24:08 GMT

Hi,

From your script it is a calculated field

Regards,

Jagan.

Re: Field not found error with preceding load

qlikviewwizard — Thu, 02 Apr 2015 05:30:24 GMT

Hi Jagan,

If you don't mind could you separate the expression and show me that as a calculated field.

Thanks in advance.

Re: Field not found error with preceding load

Thu, 02 Apr 2015 05:31:52 GMT

try once

if([DEPTNO] < $(DEPTNO), 1, 0,DEPTNO) as BS_Flag,

if([DEPTNO] < $(DEPTNO), 0, 1,DEPTNO) as PL_Flag;

Re: Field not found error with preceding load

qlikviewwizard — Thu, 02 Apr 2015 05:37:42 GMT

This is the sample qvw provided by Qliktech. I don't have source files.