topic Re: Lower case all field names, not field values automatically in App Development

Lower case all field names, not field values automatically

Panv — Tue, 07 Feb 2023 21:38:29 GMT

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;

Re: Lower case all field names, not field values automatically

Vegar — Fri, 10 Feb 2023 07:04:31 GMT

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;

Re: Lower case all field names, not field values automatically

vinieme12 — Wed, 08 Feb 2023 02:28:45 GMT

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;