topic Field Not Found in Search the Community

Field Not Found

Conniej — Fri, 26 Feb 2021 22:30:15 GMT

I continue to get this error "Field 'TransPostDate' not found" when using this string. I know the field name is correct in the QVD file so I can't determine what is causing this error.

WHERE Date(Date#(“TransPostDate”,'MM-DD-YYYY hh:mm:ss'))>=Date(Date#('01-01-2020','MM-DD-YYYY hh:mm:ss'));

Re: Field Not Found

Saravanan_Desingh — Fri, 26 Feb 2021 22:40:10 GMT

Try something like this,

WHERE Date(Date#([TransPostDate],'MM-DD-YYYY hh:mm:ss'))>=Date(Date#('01-01-2020','MM-DD-YYYY hh:mm:ss')); WHERE Date(Date#(TransPostDate,'MM-DD-YYYY hh:mm:ss'))>=Date(Date#('01-01-2020','MM-DD-YYYY hh:mm:ss'));

Re: Field Not Found

Conniej — Thu, 04 Mar 2021 21:30:17 GMT

Thank you for replying, I tried both of these suggestions and it still returns the same error.

HERE is what the output shows when it identifies the error.

Started loading data

Branches << Branches (QVD (row-based) optimized) Lines fetched: 39 Households << Households (QVD (row-based) optimized)

Lines fetched: 416,846 Services << Services (QVD (row-based) optimized) Lines fetched: 103 Calendar << Calendar (QVD (row-based) optimized) Lines fetched: 2,074 Accounts << Accounts Lines fetched: 4,005 Transactions << ShareTransactions

Lines fetched: 0

Transactions << LoanTransactions

Lines fetched: 989,594

The following error occurred:

Field 'TransPostDate' not found

The error occurred here:

Data has not been loaded. Please correct the error and try loading again.

HERE is the entire script, I've bolded the line I think is causing the error:

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 CreateSearchIndexOnReload=1;
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';

Search Exclude '*Key','*Balance*','TransComment','TransDescription';

Branches:
LOAD * FROM [lib://Transform/Branches.qvd] (qvd);

Households:
LOAD * FROM [lib://Transform/Households.qvd] (qvd);

Services:
LOAD * FROM [lib://Transform/Services.qvd] (qvd);

Calendar:
LOAD * FROM [lib://Transform/Calendar.qvd] (qvd);

Accounts:
LOAD * FROM [lib://Transform/Accounts.qvd] (qvd)
where date("AccountCloseDate") >= '01/01/2020' or 'AccountCloseDate'='NULL' or 'AccountCloseDate'='-';

Transactions:
LOAD * FROM [lib://Transform/ShareTransactions.qvd] (qvd)
WHERE Date(Date#([TransPostDate],'MM-DD-YYYY hh:mm:ss'))>=Date(Date#('01-01-2020','MM-DD-YYYY hh:mm:ss'));

Concatenate(Transactions)
LOAD * FROM [lib://Transform/LoanTransactions.qvd] (qvd)
WHERE Date("TransPostDate") >= '02/01/2020';

Concatenate(Transactions)
LOAD * FROM [lib://Transform/AccountTransactions.qvd] (qvd)
WHERE Date("TransPostDate") >= '02/01/2020';

ZipBoundaries:
LOAD * FROM [lib://Transform/ZipBoundaries.qvd] (qvd);
// Tag Field Zip With '$geoname';
// Tag Field ZipArea With '$geomultipolygon';

Re: Field Not Found

marcus_sommer — Fri, 05 Mar 2021 11:11:12 GMT

Your Transactions qvd's seems to be not equally because your script didn't break by the first two qvd's else by the third:

...
Transactions << ShareTransactions

Lines fetched: 0

Transactions << LoanTransactions

Lines fetched: 989,594

The following error occurred:

Field 'TransPostDate' not found
...

which means your wanted field is there differently named - Qlik treats fields, variables and values case-sensitive.

Beside this within the first condition by transactions you apply date#() instead timestamp#() for your timestamp-pattern of: 'MM-DD-YYYY hh:mm:ss'.

- Marcus

Re: Field Not Found

Conniej — Fri, 05 Mar 2021 17:52:22 GMT

Thanks Marcus, I'll try this and let you know if it resolves this. I appreciate it.