Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am creating a qvd monitoring app
We are looping through all qvds in a directory and reviewing the date fields within the QVDs
Some qvds have their date fields as :Year, Month, Day
Others have it in lower case: year, month, day
These are the only two scenarios of field names for the date fields Im looking at
Using the autogenerate function to get a max date generated for each qvd
Everything is working as expected however I'm not able to get the loop to work for the different cases in the field names for different qvds
How can I get the code to work in either case? Any way to force aliasing of field names such that all field names themselves are lower case ? (I don't care about field values)
sample script :
QVDfileInfo:
LOAD
"Directory",
"FileName",
"Field",
"Refresh Frequency" as refreshSch,
Team
Resident QVDList;
for vRow = 1 to NoOfRows('QVDfileInfo')
Let vQvdPath = Peek('Directory',vRow-1,'QVDfileInfo');
Let vQvdFile = Peek('FileName',vRow-1,'QVDfileInfo');
Let vField = Peek('Field',vRow-1,'QVDfileInfo');
Let vrefreshSch = Peek('refreshSch',vRow-1,'QVDfileInfo');
Let vTeam = Peek('Team',vRow-1,'QVDfileInfo');
if '$(vField)' = 'Year,Month,Day' Then
temp1:
LOAD *
FROM [$(vQvdPath)/$(vQvdFile)](qvd);
NoConcatenate
temp:
Load Distinct
year&month&day as Date_temp // This is where it fails if a qvd has Year/Month/Day as field names
Resident temp1;
Drop Table temp1;
Let vField = 'Date_temp';
trace('$(vField)');
ValidationTable:
LOAD
'$(vQvdPath)' as QvdPath,
'$(vQvdFile)' as QvdFile,
'$(vrefreshSch)' as "Refresh Schedule",
'$(vTeam)' as Team,
date(date#(max(FieldValue('$(vField)', recno())), 'YYYYMMDD'), 'MM-DD-YYYY') as File_Partition_Date
AUTOGENERATE FieldValueCount('$(vField)');
drop table temp;
End If
Next
drop table QVDfileInfo;
exit script;
take a look at Alias. it might help you to harmonise your temp1 table field names before you do the resident load.
Add the following script in the beginning of your script.
Alias Name as name, Month as month, Day as day;
Use a Mapping Table to rename the fields before loading
Add below Mapping Load before the start of the script( before QvdInfo)
LCaseFields:
Mapping Load * Inline [
FromName,ToName
Year,year
Month,month
Day,day
];
Then insert highlighted row before temp;
temp1:
LOAD *
FROM [$(vQvdPath)/$(vQvdFile)](qvd);
Rename fields using LCaseFields;
NoConcatenate
temp:
Load Distinct
year&month&day as Date_temp // This is where it fails if a qvd has Year/Month/Day as field names
Resident temp1;