3 Replies Latest reply: Apr 7, 2017 1:55 PM by Lee Mychajluk RSS

    What's wrong with this LOAD?

    Lee Mychajluk

      I load a table from a QVD:

      QUALIFY *;
      UNQUALIFY EMPLOYEE_ID, MGR_ID1;
      EmpDataTemp:

      LOAD * FROM $(vEmpQVD) (qvd);

      UNQUALIFY *;

       

      That works fine. The QVD has a field called HRMS_AS_OF_DATE. I want to populate a variable in my load script with the Max value of this field. So, I do this (based on other posts I've found here):

       

      Temp:
      Load Max(EmpDataTemp.HRMS_AS_OF_DATE) AS MaxValue RESIDENT EmpDataTemp;
      Let vLastQVDUpdateDate=FieldValue('MaxValue',1);
      Drop Table Temp;

       

      I've tried this w/ and w/o fully qualifying the Table Name as part of the field, but I keep getting an error that the HRMS_AS_OF_DATE is not a valid field name, and I'm not sure why...

       

      Anyone see anything wrong with this? Thanks!

        • Re: What's wrong with this LOAD?
          Gysbert Wassenaar

          Make sure your qvd file really has a field with the exact name HRMS_AS_OF_DATE. Note that Qlikview is case sensitive with regards to field names. That means HRMS_AS_OF_DATE, hrms_as_of_date and Hrms_As_Of_Date are three different fields.

            • Re: What's wrong with this LOAD?
              Lee Mychajluk

              Thanks for the reply. Maybe it'll help if I include the whole script....

               

              Basically, I'm trying to do an incremental load. The first run-through works and creates a qualified field name of HRMS_EMP.HRMS_AS_OF_DATE. Once the QVD is created, on the next run I want to load the deltas into the table HRMS_EMP table and store them in the QVD. The IF statement determines the start date of the loop based on the last HRMS_AS_OF_DATE from the data loaded from the QVD. With the script as-is below, I think I end up with 2 sets of fields in the table - one set qualified as HRMS_EMP.xxx and the other as EmpDataTemp.xxx, (I truncated the list of fields, but you get the idea...)

               

              I have to loop through the SQL by date because I'm using a function to build a slowly changing dimensions table, and the function only gives data as per a certain date. I think maybe it has something to do with my table names and the loop and the fact that I'm not qualifying the field names, but if I qualify the field names in the loop I end up with multiple temp tables (EmpDataTemp-1. EmpDataTemp-2, etc...

               

              So, maybe I'm going about this wrong? TIA for any pointers.

               

              //*******************************************

              vProcDate=vStartDate // Use Default Start Date from Config. Could change below if QVD exists.

               

              // Check to see if EmpQVD Exists and load if it does

              LET vTest=FileTime(vEmpQVD);

              LET vEmpQVDExists = isnull(FileTime(vEmpQVD));  // if qvd exists then 0 else -1

              IF $(vEmpQVDExists)=0 THEN

               

                // Load Contents of Current Employee QVD into Temp Table

                QUALIFY *;

                UNQUALIFY EMPLOYEE_ID, MGR_ID1;

                EmpDataTemp:

                LOAD * FROM $(vEmpQVD) (qvd);

                UNQUALIFY *;

               

                //EXIT SCRIPT;

               

                // Update the Start Date based on the last update in the QVD

               

                // Get last HRMS_UPDATE_DATE from HRMS+. Not sure yet if I'm going to use this.

                Temp:

                Load LAST_UPDATE as MaxValue;

                SQL SELECT MAX(HRMS_AS_OF_DATE) as LAST_UPDATE FROM fn_getEmpCoreAttributes(getdate(), default, default);

                Let vLastHRMSUpdateDate=FieldValue('MaxValue',1);

                Drop Table Temp;

               

                //EXIT Script;

               

                // Get last HRMS_UPDATE_DATE from HRMS_EMP

                Temp:

                Load Max(EmpDataTemp.HRMS_AS_OF_DATE) AS MaxValue RESIDENT EmpDataTemp;

                Let vLastQVDUpdateDate=FieldValue('MaxValue',1);

                Drop Table Temp;

               

                //Set the Process Start Date to the last day in the QVD

                LET vProcDate=vLastQVDUpdateDate;

               

              ENDIF

               

              DO WHILE vProcDate<Today()

               

                //Where conditions using the HRMS+ IRS

                LET vFromExpr='HRMSInterface.dbo.fn_getEmpCoreAttributes(' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39) & ',default,default)';

                LET vFromExpr=vFromExpr &  ' WHERE CAST(UPDATED_DATE AS DATE)=' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39); //Adds clause to only pull the modified entries from this date

               

              // QUALIFY *;

              // UNQUALIFY EMPLOYEE_ID, MGR_ID1;

                EmpDataTemp:

                LOAD HRMS_AS_OF_DATE,

                PERSON_ID,

                COMPANY_CODE,

                EMPLOYEE_ID,

                FULL_NAME,

                FIRST_NAME,

                MIDDLE_NAME,

                LAST_NAME,

                MGR_ID1

                ;

                SQL SELECT HRMS_AS_OF_DATE,

                PERSON_ID,

                COMPANY_CODE,

                EMPLOYEE_ID,

                FULL_NAME,

                FIRST_NAME,

                MIDDLE_NAME,

                LAST_NAME,

                MGR_PERSON_ID1 AS MGR_ID1

                FROM $(vFromExpr)

                ;

              // UNQUALIFY *;

               

                STORE EmpDataTemp INTO $(vEmpQVD) (qvd);

                DROP Table EmpDataTemp;

               

                //Let vProcDate=MonthEnd(AddMonths(vProcDate,1)); //Increment Processing Date by 1 Month

                Let vProcDate=vProcDate+1; //Increment Processing Date by Day

               

               

              LOOP

               

              //Store the Temp tables into QVD file and Drop the Temp table

              STORE EmpDataTemp INTO $(vEmpQVD) (qvd);

              DROP Table EmpDataTemp;

               

               

              //Reload the QVD File with the working Table Name

              QUALIFY *;

              UNQUALIFY EMPLOYEE_ID, MGR_ID1;

              HRMS_EMP:

              LOAD * from $(vEmpQVD) (qvd);

              UNQUALIFY *;

               

               

               

               

              EXIT SCRIPT;

                • Re: What's wrong with this LOAD?
                  Lee Mychajluk

                  I think I got it working. I had to stop using the EmpDataTemp table name and just use HRMS_EMP everywhere and trust Qlik to concatenate the data properly. I also had to stop QUALIFYING all but the final load from the QVD, which seemed to fix the Table-1, Table-2, etc... issue.

                   

                  ********************************************************************************************************************

                  vProcDate=vStartDate // Use Default Start Date from Config. Could change below if QVD exists.

                   

                   

                  // Check to see if EmpQVD Exists and load if it does

                  LET vTest=FileTime(vEmpQVD);

                  LET vEmpQVDExists = isnull(FileTime(vEmpQVD));  // if qvd exists then 0 else -1

                  IF $(vEmpQVDExists)=0 THEN

                   

                   

                    // Load Contents of Current Employee QVD into Temp Table

                   

                   

                    HRMS_EMP:

                    LOAD * FROM $(vEmpQVD) (qvd);

                   

                    // Update the Start Date based on the last update in the QVD

                   

                    // Get last HRMS_UPDATE_DATE from HRMS+. Not sure yet if I'm going to use this.

                    Temp:

                    Load LAST_UPDATE as MaxValue;

                    SQL SELECT MAX(HRMS_AS_OF_DATE) as LAST_UPDATE FROM fn_getEmpCoreAttributes(getdate(), default, default);

                    Let vLastHRMSUpdateDate=FieldValue('MaxValue',1);

                    Drop Table Temp;

                   

                    // Get last HRMS_UPDATE_DATE from HRMS_EMP

                    Temp:

                    //Load Max(EmpDataTemp.HRMS_AS_OF_DATE) AS MaxValue RESIDENT EmpDataTemp;

                    Load Max(HRMS_AS_OF_DATE) AS MaxValue RESIDENT HRMS_EMP;

                    Let vLastQVDUpdateDate=FieldValue('MaxValue',1);

                    Drop Table Temp;

                   

                    //Set the Process Start Date to the last day in the QVD

                    LET vProcDate=vLastQVDUpdateDate;

                   

                  ENDIF

                   

                  DO WHILE vProcDate<Today()

                   

                   

                    //Where conditions using the HRMS+ IRS

                    LET vFromExpr='HRMSInterface.dbo.fn_getEmpCoreAttributes(' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39) & ',default,default)';

                    LET vFromExpr=vFromExpr &  ' WHERE CAST(UPDATED_DATE AS DATE)=' & Chr(39) & Date(vProcDate, 'MM/D/YYYY') & Chr(39); //Adds clause to only pull the modified entries from this date

                   

                    HRMS_EMP:

                    LOAD HRMS_AS_OF_DATE,

                    PERSON_ID,

                    COMPANY_CODE,

                    EMPLOYEE_ID,

                    FULL_NAME,

                    FIRST_NAME,

                    MIDDLE_NAME,

                    LAST_NAME

                    ;

                    SQL SELECT HRMS_AS_OF_DATE,

                    PERSON_ID,

                    COMPANY_CODE,

                    EMPLOYEE_ID,

                    FULL_NAME,

                    FIRST_NAME,

                    MIDDLE_NAME,

                    LAST_NAME

                    FROM $(vFromExpr)

                    ;

                   

                    //Let vProcDate=MonthEnd(AddMonths(vProcDate,1)); //Increment Processing Date by 1 Month

                    Let vProcDate=vProcDate+1; //Increment Processing Date by Day

                   

                  LOOP

                   

                  //Store the tables into QVD file

                  STORE HRMS_EMP INTO $(vEmpQVD) (qvd);

                  DROP Table HRMS_EMP;

                   

                  //Reload the QVD File with the working Table Name

                  QUALIFY *;

                  UNQUALIFY EMPLOYEE_ID, MGR_ID1;

                  HRMS_EMP:

                  LOAD * from $(vEmpQVD) (qvd);

                  UNQUALIFY *;