Check if column exists when table name is unkown.

michael_andrews — Tue, 02 Jan 2018 21:51:59 GMT

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.

Re: Check if column exists when table name is unkown.

petter — Tue, 02 Jan 2018 23:19:16 GMT

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;

Re: Check if column exists when table name is unkown.

michael_andrews — Wed, 03 Jan 2018 17:51:45 GMT

This did the trick, thanks!

topic Check if column exists when table name is unkown. in Connectivity & Data Prep

Check if column exists when table name is unkown.

Re: Check if column exists when table name is unkown.

Re: Check if column exists when table name is unkown.