Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Hi,
You have commented the DEPTNO field in line 23, remove that comments and your script works as expected.
Regards,
Jagan.
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
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);
does your file [General Ledger.xls] contains Closing Amount ?
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.
Hi Jagan,
If you don't mind could you separate the expression and show me that as a calculated field.
Thanks in advance.
Hi
try once
if([DEPTNO] < $(DEPTNO), 1, 0,DEPTNO) as BS_Flag,
if([DEPTNO] < $(DEPTNO), 0, 1,DEPTNO) as PL_Flag;
This is the sample qvw provided by Qliktech. I don't have source files.