2 Replies Latest reply: Jan 3, 2018 12:51 PM by Michael Andrews RSS

    Check if column exists when table name is unkown.

    Michael Andrews

      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 Skjolden

          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;