Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Panv
Contributor II
Contributor II

Lower case all field names, not field values automatically

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;

 

  

Labels (1)
2 Replies
Vegar
MVP
MVP

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;

vinieme12
Champion III
Champion III

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;

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.