Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am getting an error when trying to load script including a NOT WILDMATCH, and I cannot find where my error is. Code is as follows (with sensitive parts redacted):
LOAD
Date(Date#(LocalDate, 'YYYY-MM-DD'), 'DD MMM YYYY') as LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile;
[Table Name]:
SELECT
LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
FROM [Redacted]
WHERE Details='Application Initialized' or Details='Catalog Initialized'
AND NOT WILDMATCH (CatalogName,'*ExecutePoShScript*','*ExecutePowerShellScript','CatalogName');
The error is as follows:
It says "LocalDate not found", but that is not the actual problem because the data (including LocalDate) loads fine without the NOT WILDMATCH.
Does anyone see what I've done wrong?
@CreepyCatLady you are already using preceding load for formatting then why don't you just put all condition within load itself.
LOAD
Date(Date#(LocalDate, 'YYYY-MM-DD'), 'DD MMM YYYY') as LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
WHERE Details='Application Initialized' or Details='Catalog Initialized'
AND NOT WILDMATCH (CatalogName,'*ExecutePoShScript*','*ExecutePowerShellScript','CatalogName');
[Table Name]:
SELECT
LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
FROM [Redacted];
It looks like you are querying a SQL database. Is that a correct observation? If so then NOT WILDMATCH () might not be a valid Query function. Try replacing it with valid SQL syntax.
@CreepyCatLady you are already using preceding load for formatting then why don't you just put all condition within load itself.
LOAD
Date(Date#(LocalDate, 'YYYY-MM-DD'), 'DD MMM YYYY') as LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
WHERE Details='Application Initialized' or Details='Catalog Initialized'
AND NOT WILDMATCH (CatalogName,'*ExecutePoShScript*','*ExecutePowerShellScript','CatalogName');
[Table Name]:
SELECT
LocalDate,
AppName,
DisplayName,
AppVersion,
AppRunTime,
AppRunTimeInSeconds,
UserDomain,
UserName,
UserFullName,
DeviceDomain,
DeviceName,
CatalogName,
TargetEntityName,
Details,
LogFile
FROM [Redacted];
Thank you, this worked to stop the error, but after the load completes, I still see several of the results that should have been filtered out.
Here is my "Where" statement:
WHERE Details='Application Initialized' or Details='Catalog Initialized'
AND NOT WILDMATCH (CatalogName,'*ExecutePoShScript*','*ExecutePowerShellScript','#CatalogName#','Moksha Auto Install Complete Product',
'Moksha.Auto.Install.Update.Synchronize','MyRdpClient','MyRdpServer','Sync-UserToDeviceEntitlementsXML-To-CTX-XA-Servers')
AND NOT WILDMATCH (DisplayName, 'Update Application Core Files','Device Cleaner','Moksha Incubator','OneDriveRingR');
And you can see here that "OneDriveRingR" is still present in the data:
There are others that should be filtered out that are still there as well. What am I doing wrong?
You are mixing AND and OR in your Where statement. Do you need brackets somewhere?
WHERE
(Details='Application Initialized' or Details='Catalog Initialized')
AND NOT WILDMATCH (...)
AND NOT WILDMATCH (...)
;
Oh my gosh, that was it. I feel foolish for not seeing that. Thank you so much for your help.