Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You could use the FieldValueCount() script function. It will return the number of distinct values in a field, which can be 0 (all the values of the field is NULL) or a positive integer. If the field does not exist it return NULL.
IsNum( FieldValueCount('aFieldName') )
will return True if the field exists and False if it doesn't. In Qlik True is -1 or anything that is not 0, False is 0.
In you script it would look like this:
FOR ...
...
LET vMcTableName = 'MasterCalendar' & vDateColumnName;
IF IsNum( FieldValueCount( '$(vDateColumnName)' ) ) THEN // vDateColumnName exists
$(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;
END IF
NEXT vIdx;
You could use the FieldValueCount() script function. It will return the number of distinct values in a field, which can be 0 (all the values of the field is NULL) or a positive integer. If the field does not exist it return NULL.
IsNum( FieldValueCount('aFieldName') )
will return True if the field exists and False if it doesn't. In Qlik True is -1 or anything that is not 0, False is 0.
In you script it would look like this:
FOR ...
...
LET vMcTableName = 'MasterCalendar' & vDateColumnName;
IF IsNum( FieldValueCount( '$(vDateColumnName)' ) ) THEN // vDateColumnName exists
$(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;
END IF
NEXT vIdx;
This did the trick, thanks!