Field not found error with preceding load

[qlikviewwizard]

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],

  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) + [Actual], peek([Closing Amount]) + Actual) as [Closing 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.

[jagan]

Hi,

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

Regards,

Jagan.

[its_anandrjs]

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

[qlikviewwizard]

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);

[Anonymous]

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

[jagan · Accepted solution]

Hi,

From your script it is a calculated field

  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) + [Actual], peek([Closing Amount]) + Actual) as [Closing Amount]

Regards,

Jagan.

[qlikviewwizard]

Hi Jagan,

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

Thanks in advance.

[#8]

Hi

try once

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

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

[qlikviewwizard]

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