I ran a script to generate a qvd with data from 2020. I changed the where clause to include data for 2021 and it is now throwing an error saying it does not recognize two fields that I did not change. Is there an issue with naming or should I have not tried to run the script multiple times to get multiple years of data in the qvd. Sorry if this is a stupid question, I am new to Qlik.
Error:
The following error occurred:
Field 'zip_code_ads.zip_code_id' not found
Here is the script. I put the fields that are throwing the error in bold.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$ ###0.00;-$ ###0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
LIB CONNECT TO [connection info];
Let vSpaceName = GetSysAttr('spaceName');
Set DashName = Pricing; // Your Dashboard name if needed.
zip_code_ads_qvd='lib://$(vSpaceName):DataFiles/zip_code_ads_$(DashName).qvd';
if FileSize('$(zip_code_ads_qvd)') > 0 then
[zip_code_ads]:
LOAD
[zip_code_ads_id],
[zip_code_ads.account_id],
[zip_code_ads.profile_id],
[zip_code_ads.ad_type_id],
[zip_code_ads.category_id],
[zip_code_ads.banner_id],
[zip_code_ads.zip_code_id],
[zip_code_ads.is_enabled],
[zip_code_ads.is_purchased],
[zip_code_ads.updated_at]
FROM [$(zip_code_ads_qvd)](qvd);
t:load Timestamp(Max([zip_code_ads.updated_at])) as max_at Resident [zip_code_ads];
last_at=Peek('max_at'); // Retrieves the last at
Drop Table t;
else
last_at = '1970-01-01 00:00:00';
[zip_code_ads]:load Null() AS [zip_code_ads] AutoGenerate 0;
endif
Trace 'zip_code_ads.last_at:$(last_at)';
Merge([zip_code_ads.updated_at]) On [zip_code_ads_id] Concatenate([zip_code_ads])
Load If(Exists([zip_code_ads_id]),'U','I') as Operation, *;
SELECT "id" AS "zip_code_ads_id",
"account_id" AS "zip_code_ads.account_id",
"profile_id" AS "zip_code_ads.profile_id",
"ad_type_id" AS "zip_code_ads.ad_type_id",
"category_id" AS "zip_code_ads.category_id",
"banner_id" AS "zip_code_ads.banner_id",
"zip_code_id" AS "zip_code_ads.zip_code_id",
"is_enabled" AS "zip_code_ads.is_enabled",
"is_purchased" AS "zip_code_ads.is_purchased",
"updated_at" AS "zip_code_ads.updated_at"
FROM "elocal"."zip_code_ads"
WHERE updated_at BETWEEN '2021-01-01 00:00:00.000' AND '2021-12-31 23:59:59.999';
Trace 'Storing into $(zip_code_ads_qvd)';
Store [zip_code_ads] into [$(zip_code_ads_qvd)](qvd);
