Hi, we have a load script that loads date fields for us. We're trying to create one generic load script we could use across multiple apps. The issue is, some of those apps load different tables, and so we are trying to find a way to check if a column exists in that data, but I don't know the table name. Is there a way to do this? Here is my load script
///////////////////////////////////////////////////////////////////////////////////////////////////
// This must be done AFTER all column names are remapped.
// That is because we must dynamically generate the final column names for each dynamically built MasterCalendar.
LIB CONNECT TO [Sql_cm_hub];
// Date (Dimension)
// Currently holds dates from 1900-2199 (109,573); limiting to 1980-2080 pulls only 30% of data
// Remember: Qlik effectively has only a "datetime" type.
TempMasterCalendar:
SQL
SELECT d.*
, FLOOR(((12 + date_month - COALESCE(client_first_fiscal_quarter, 1)) % 12) / 3 ) + 1 AS date_fiscal_quarter
FROM date_dim AS d
INNER JOIN client ON client_catalog = '$(cmDbCat)'
WHERE date_year BETWEEN 1980 AND 2080;
DISCONNECT;
// Columns that each need their own custom MasterCalendar.
TempMcColumnList:
LOAD * INLINE [
DateColumnName
BadgeCertAssertionDate
BadgeCertDate
BadgeExamAssertionDate
BadgeExamDate
BadgeOtherRequirementAssertionDate
BadgeOtherRequirementDate
CandidateLastUpdatedDate
CandidateCreatedDate
CredentialCatalogAvailableDate
CredentialCatalogRetireDate
CredentialAchievedDate
CredentialInactiveDate
CredentialExpireDate
CredentialLatestExtendDate
CredentialLastUpdatedDate
EmailSentDate
ExamLastUpdatedDate
ExamAchievedDate
ExamCreatedDate
ExamExpireDate
ExamGradedDate
ExamLastImportedDate
LoginDate
OtherRequirementAchievedDate
OtherRequirementExpireDate
OtherRequirementLastUpdatedDate
];
LET vColumnCount = NoOfRows('TempMcColumnList');
FOR vIdx = 0 TO vColumnCount - 1
LET vDateColumnName = Peek('DateColumnName', vIdx, 'TempMcColumnList');
LET vDateColumnName = Peek('DateColumnTable', vIdx, 'TempMcColumnList');
LET vMcTableName = 'MasterCalendar' & vDateColumnName;
//right here I need to find out if this column actually exists anywhere in the data
//if it does not, I'd just like to continue on with the for.
$(vMcTableName):
LOAD date_value AS $(vDateColumnName)
, date_year AS $(vDateColumnName)Year
, date_month AS $(vDateColumnName)Month
, date_day AS $(vDateColumnName)Day
, date_day_name AS $(vDateColumnName)DayName
, date_month_name AS $(vDateColumnName)MonthName
, date_quarter AS $(vDateColumnName)Quarter
, date_fiscal_quarter AS $(vDateColumnName)FiscalQuarter
, date_day_of_week AS $(vDateColumnName)DayOfWeek
, date_day_of_year AS $(vDateColumnName)DayOfYear
, date_month_end AS $(vDateColumnName)IsEndOfMonth
, WEEK(date_value, 6) AS $(vDateColumnName)WeekOfYear // Sunday is the first day
RESIDENT TempMasterCalendar;
NEXT vIdx;
DROP TABLE TempMasterCalendar;
DROP TABLE TempMcColumnList;
Basically, in the for loop, I'd like to be able to see whatever column I'm on, and check if it exists in the data, and if it does not, then just continue the for loop.
