Incremental load script error - Qliksense

[balajisj30]

Hi People

I am trying to write incremental load script (insert, update, delete) in qliksense. But i am getting an error - Field 'a' not found.

Please find below my incremental load script,

[incremental]:
LOAD
[InvoiceID],
[PayerAccountId],
[LinkedAccountId],
[RecordType],
[RecordID],
[BillingPeriodStartDate],
[BillingPeriodEndDate],
[InvoiceDate],
[TotalCost]
FROM [lib://aws bill & bpo/actual.xlsx]
(ooxml, embedded labels, table is Sheet1) where InvoiceDate > $(last_updated_date);

Concatenate
LOAD
[InvoiceID],
[PayerAccountId],
[LinkedAccountId],
[RecordType],
[RecordID],
[BillingPeriodStartDate],
[BillingPeriodEndDate],
[InvoiceDate],
[TotalCost]
FROM [lib://aws bill & bpo/actual1.qvd](qvd) Where not Exists(InvoiceID);

Inner Join
Load [InvoiceID]
from [lib://aws bill & bpo/actual.xlsx]
(ooxml, embedded labels, table is Sheet1);

Store incremental into [lib://aws bill & bpo/actual1.qvd](qvd);

Drop Table incremental;

Exit Script;

error screenshot

Please help me to resolve this issue.

Thanks in Advance,

Balaji

Edit 1 -

Main section code,

Set dataManagerTables = '','Sheet1';
//This block renames script tables from non generated section which conflict with the names of managed tables

For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;

Unqualify *;

[Sheet1]:
LOAD
[InvoiceID],
[PayerAccountId],
[LinkedAccountId],
[RecordType],
[RecordID],
[BillingPeriodStartDate],
[BillingPeriodEndDate],
[InvoiceDate],
[TotalCost]
FROM [lib://aws bill & bpo/actual.xlsx]
(ooxml, embedded labels, table is Sheet1);

store Sheet1 into [lib://aws bill & bpo/actual1.qvd] (qvd);

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [BillingPeriodStartDate], [BillingPeriodEndDate], [InvoiceDate] USING [autoCalendar] ;

Exit Script;

I have run main section separately, it ran successfully and fetched the records.

[Yoshidaqlik]

Hi,

I believe the error should be in the section Main

may have any lyrics a that was typed by accident

you can copy what's in the section Main

Regards

[balajisj30]

Hi @Yoshidaqlik ,

I have included main section script in the question. Please have a look. Thanks in Advance.

[Yoshidaqlik · Accepted solution]

seeing the entire script the variable $(last_updated_date)

is used in a Where condition but has never been previously declared with any value

Regards