Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

lmychajluk
New Contributor

What's wrong with this LOAD?

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!

3 Replies

Re: What's wrong with this LOAD?

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.


talk is cheap, supply exceeds demand
lmychajluk
New Contributor

Re: What's wrong with this LOAD?

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;

lmychajluk
New Contributor

Re: What's wrong with this LOAD?

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 *;