Solved: Missing data in table with traffic lights - Qlik Community

Report Inappropriate Content · ‎2013-03-22

We have a dashboard with the following expression

= if (

(TRAINING_REGISTRATION_STATUS='Completed'

AND MAND_TRAINING_EXPIRES = 1

AND((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 14)

OR (TRAINING_REGISTRATION_STATUS='Completed'

AND MAND_TRAINING_EXPIRES = 0),

1,

if( TRAINING_REGISTRATION_STATUS='Completed' AND

MAND_TRAINING_EXPIRES = 1 AND

((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) < 18 AND

((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 14,2,

if( TRAINING_REGISTRATION_STATUS='Completed' AND

MAND_TRAINING_EXPIRES = 1 AND

((vReferenceYear - TRAINING_COMPLETION_YEAR)*12 + vReferenceMonth - TRAINING_COMPLETION_MONTH) >= 18,3,3)

))

See the output of one with correct data and one with incorrect data. The one with incomplete data has no training records availlable. In this situation a table with all red traffic lights must be displayed.

Many thanks in advance!

John

Report Inappropriate Content · ‎2013-03-22

So not much direction in this. I have checked ODBC settings already.

The loop is trying to establish a connection so the loop is with dynamic files. So what fixed ODBC settings must be set/changed?

When I click User DSN configure I got an error message something wrong with ODBC (I had this before but was solved)

John

View solution in original post

Report Inappropriate Content · ‎2013-03-22

Hello John,

Your expression seems good, As I have understood is that your expression will return following results :

1 if Your Condition < 14

2 if Your Condition <= 14 and < 18

3 if Your Condition >= 18

3 else case.

The data with no training records is showing Red color i.e 3 or else case. Isn't it?

How are you using these 1, 2, and 3 to get data. Please elaborate or attach your qvw.

Thanks,

itangad

Report Inappropriate Content · ‎2013-03-22

Yes when there are no training records it must be red and having a table of 6 rows (now only 1 row).

The status of 1,2,3 is generated by the given if expression so what do you mean?

Sending QV app can this be done without the containing data?

John

Report Inappropriate Content · ‎2013-03-22

The case in which you are stuck is that when no training records are there. Right? and for that you need to show Red Color(which is being shown)

The issue left is that you have 6 rows with no training records in the model, so you want to link those records. ?

Yes you can send me the qvw, which will contain the preloaded data. you do not have to send me the data files.

I can give a try to look into this.

Thanks,

itangad.

Report Inappropriate Content · ‎2013-03-22

And how can I send you the qvw app? It is personnel data so handle it with care please.

Another thing is I have a load error see this post http://community.qlik.com/message/325197#325197

I don't know what the cause of this message. I've looked in System/User DSN and point it to another file but no success.

John

Report Inappropriate Content · ‎2013-03-22

To upload the qvw, Click at the Use advanced editor link, at the upper right corner of the editor.

And I would try to look into that as well.

thanks,

itangad

Report Inappropriate Content · ‎2013-03-22

Hi,

Where to find the link? I checked all my upper right corners but I can't find it.

John

Report Inappropriate Content · ‎2013-03-22

Please find attached screenshot of the link,

When you reply on any comment, on that editor check the upper right corner.

thanks,

Report Inappropriate Content · ‎2013-03-22

So this is the load script:

/*

Scriptfile for loading and transforming excel sheets containing source data for the Perrsonal Performance KPI dashboard

There are 5 sources in total in 5 directories. The scripts checks the 5 directories for xlsx file content. The files are

loaded into 6 QV tables (Financial, Time_Sheets, Client_Satisfaction, Career_Learning, My_Path, CV_Update). The PERSONEL_ID is

created during load of each table.

Data from n-excel files are appended (concatenated) after eachother.

Data format in sources are fixed and cannot change, e.g. date formats etc.

The loaded fields (columns)are defined in the script. Any additional fields will not be included without editing the script

All derived and renamed fields are 'Capitalized'

Changes 25/7 2012

1. added if([Form status] = 'Complete',4 at the load od my path status

2. SET the vPath as a top variable and the adding it to other PATHS by let statements

3. the Variable vPath is now defined on the sheet (second tab)

Changes 1/8 2012

1. Comment redundant fields (some redundancy is kept). Does not speed up the load since QV is in-memory!

2. Add conditional load for employees table: AND [EG] = 'CSS-P' AND match([Practice],'B50','B60','B61','B63','B65') > 0

Changes 20/8 2012

1. Hard coded so that only sheet1 is always loaded,

e.g LET sheetName = 'Sheet1'; //purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); //remove any special characters

2. Add extra My path status value 9 if one of the four statuses does not exist if([Objective Setting] = 'Completed',4,9

3. Load of mandatory-trainings

*/

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

SET TimeFormat='h:mm:ss';

SET DateFormat='D-M-YYYY';

SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

SET MonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='ma;di;wo;do;vr;za;zo';

//Get the relod date to be passed to the macro

LET vScriptReload=today();

// Set the variables to be passed to the macro (see CTRL M). This does not work with LET and the extra = sign is n=indeed necessary, looks like a workaround!

SET vPCode = =GetFieldSelections(PRACTICE_CODE);

SET vMonth = =GetFieldSelections(KPI_MONTH);

//Define paths to source data

LET vDocPath = left(DocumentPath(),Len(DocumentPath())-(12+Len(DocumentName()))); // The path up until the document

LET vExPath = '$(vDocPath)' & '4. Results\';

LET vCSVPath ='$(vDocPath)' & '3. Qlikview\NL_CSV_OUTPUT\';

LET vPath = '$(vDocPath)' & '2. Source data as used for QlikView\';

LET vPathCSP = '$(vPath)' & '1. Employee data\';

LET vPathF = '$(vPath)' & '2. Financial data\';

LET vPathCL = '$(vPath)' & '3. Training data\';

LET vPathMP = '$(vPath)' & '4. MyPath data\';

LET vPathCV = '$(vPath)' & '5. Ematch data\';

LET vPathTS = '$(vPath)' & '6. Hours deviation data\';

LET vPathMT = '$(vPath)' & '7. Mandatory training data\';

LET vPathLv = '$(vPath)' & '8. Leave data\';

LET vPathCrt = '$(vPath)' & '9. Certificates\';

////////////////////////////

//0. Calendar Master Data //

////////////////////////////

LET vMinDate = num(date#('01-01-2010','dd-mm-yyyy'));

LET vMaxDate = num(date#('12-31-2015','dd-mm-yyyy'));

LET vNullDate = num(date#('00-00-0000','dd-mm-yyyy'));

MasterCalendar:

LOAD

Date(IterNo() + Date($(vMinDate))) AS ReportDate,

Week(Date(IterNo() + Date($(vMinDate)))) AS CalendarWeek,

Month(Date(IterNo() + Date($(vMinDate)))) AS ReportMonth,

Year(Date(IterNo() + Date($(vMinDate)))) AS ReportYear

AUTOGENERATE 1 WHILE Date(IterNo() + Date($(vMinDate))) <= Date($(vMaxDate));

//////////////////////////

//1. Make employee data //

//////////////////////////

Let vDir = '$(vPathCSP)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Employees:

LOAD Distinct //Added distinct since the belgium dummy data is not unique, it is made from the training data

'1' AS IND_EMPLOYEE_DATA,

[Pers.nr.] AS EMPLOYEE_ID,

// ID is required for export functionality

[Pers.nr.] AS ID,

[Practice] AS PRACTICE_CODE,

//[EG],

[Aanspreeknaam] AS EMPLOYEE_NAME,

[Emailadres]

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Pers.nr.]) AND

match([Practice],'B50','B60','B61','B63','B65') > 0; //Added the BE Gxx practices

// FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Pers.nr.]) AND [EG] = 'CSS-P' AND

// match([Practice],'B50','B60','B61','B63','B65') > 0; //Added the BE Gxx practices

ENDIF

//////////////////////////////////////////////

//2. Load the source data for Financial KPIs//

//////////////////////////////////////////////

LET vDir = '$(vPathF)' & '*.xlsx';

//Find files in path and loop over the files and append data to the tables table

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables: //make table tables from the DB info created by the ODBC connection

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

//TABLE_CAT contains the full path to the file with the filename and extention

//We will loop through this set of sheet names.

FOR i = 0 to NoOfRows('tables')-1

// Assign the TABLE_NAME to the variable "sheetName".

// TABLE_NAME that contain spaces will be enclosed in single quotes.

// TABLE_CAT is the filepath+filename+extension.

// The purgeChar function will remove any quotes AND dollar signs.

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Financial: //The name of the QlikView table

//Concatenate NoConcatenate to control the behavior that tables are appended to each other

LOAD Distinct

'1' AS IND_FINANCIAL_DATA,

[Employee number] AS EMPLOYEE_ID, //The key

//

// sickness gauge - Peter Stuart

//

Num#([Billable_hours],'#',',') AS BillableHours,

Num#([Idle],'#',',') AS Idle,

Num#([Management & Internal meetings],'#',',') AS Management_Internal_meetings,

Num#([Vacation],'#',',') AS Vacation,

Num#([Paid absence],'#',',') AS Paid_absence,

Num#([Sickness],'#',',') AS Sickness,

Num#([ARVE],'#',',') AS ARVE,

[COR] as COR,

Num#([MU_direct],'#','.') AS MU_direct

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Employee number]);

ENDIF

// LOAD

// BillableHours + Idle + Management_Internal_meetings + Vacation + Paid_absence + Sickness AS TotalHours,

// Sickness / TotalHours as SicknessRatio;

//////////////////////////////////////////////

//3. Load the source data for Training data //

//////////////////////////////////////////////

Let vDir = '$(vPathCL)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Career_Learning:

LOAD distinct

[Activity Code] AS TRAINING_CODE,

[Activity Name] AS TRAINING_NAME,

[LocalEmpNumber] AS EMPLOYEE_ID,

[Registration Status] AS TRAINING_REGISTRATION_STATUS,

Year([Attempt Completion Date]) AS TRAINING_COMPLETION_YEAR,

Month([Attempt Completion Date]) AS TRAINING_COMPLETION_MONTH,

MonthStart([Attempt Completion Date]) AS TRAINING_COMPLETION_YYYYMM

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([LocalEmpNumber]);

ENDIF

////////////////////////////////////////////

//4.Load the source data for My Path data //

////////////////////////////////////////////

Let vDir = '$(vPathMP)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

My_Path:

LOAD

'1' AS IND_MYPATH_DATA,

[Pers.nr.] AS EMPLOYEE_ID,

[Assignment Appraisals] AS MYPATH_ASSIGNMENTS,

[Objective Setting] AS MYPATH_STATUS_CODE,

[Mid Year Self Assessment] AS MYPATH_MID_REVIEW,

[Year End Self Assessment] AS MYPATH_END_REVIEW

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Pers.nr.]);

ENDIF

////////////////////////////////////////////

//5. Load the source data for Ematch data //

////////////////////////////////////////////

Let vDir = '$(vPathCV)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

CV_Update:

LOAD

'1' AS IND_CV_DATA,

[SAP-ID] AS EMPLOYEE_ID,

DayStart([Last update CV]) AS CV_LAST_UPDATE_DATE

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([SAP-ID]);

ENDIF

//////////////////////////////////////////////////////////

//6. Load the source data for Clarity data (Time sheets)//

//////////////////////////////////////////////////////////

Let vDir = '$(vPathTS)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Time_Sheets:

LOAD

[Personnel #] AS EMPLOYEE_ID,

If(Variance < 0, 1,0) AS TIME_SHEET_LATE ,

DATE#([Start Date]) AS Date_Begin_Week,

Date#([End Date]) AS Date_End_Week,

week(date#([Start Date])) as Week

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)], header is 7 lines) where IsNum([Personnel #]);

ENDIF

///////////////////////////////////////////////////////////

//7. Load the source data Mandatory training course data //

///////////////////////////////////////////////////////////

Let vDir = '$(vPathMT)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

MandatoryTraining:

LOAD

[Activity Code] AS TRAINING_CODE, //KEY for Training table

[Activity Code] AS MAND_TRAINING_CODE,

[Activity Name] AS MAND_TRAINING_NAME,

[Mand. Group] AS MAND_TRAINING_GROUP,

[Expires] AS MAND_TRAINING_EXPIRES,

1 AS MAND_TRAINING_IND_DONE

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

//////////////////////////////////////////////////////////

//8. Load the source data for Leave data (Verlof)//

//////////////////////////////////////////////////////////

Let vDir = '$(vPathLv)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Leave:

LOAD

[Personnel Number] AS EMPLOYEE_ID,

Num#([Rem. on key date],'#',',') AS RemainingLeave

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Personnel Number]) AND IsNull([Name]);

ENDIF

//////////////////////////////////////////////////////////

//9. Load the source data for Certificates //

//////////////////////////////////////////////////////////

Let vDir = '$(vPathCrt)' & '*.xlsx';

FOR Each file in FileList(vDir)

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

LET file_path = purgeChar(purgeChar(peek('TABLE_CAT', i, 'tables'), chr(39)), chr(36));

IF sheetName='Sheet1' THEN

Certificates:

LOAD

[Personnel Number] AS EMPLOYEE_ID,

//yellow is if end date is between 2 months away from report month and 4 month passed the report month

if((year(monthstart(today(),-1)) - year([End Date]))*12 + month(monthstart(today(),-1)) - month([End Date]) < -2,0,

if((year(monthstart(today(),-1)) - year([End Date]))*12 + month(monthstart(today(),-1)) - month([End Date]) <= 3,1,3

)

) AS VALIDITY,

[End Date] AS END_DATE,

[Name] AS CERTIFICATE,

[Proficiency] AS LEVEL

FROM $(file_path) (ooxml, embedded labels, table is [$(sheetName)]) where IsNum([Personnel Number]);

ENDIF

////////////////////////////////

//10.1 Left Join Man Train //

////////////////////////////////

MAN_TRAININGS:

LOAD Distinct

MAND_TRAINING_NAME,

MAND_TRAINING_CODE AS TRAINING_CODE,

MAND_TRAINING_GROUP

RESIDENT MandatoryTraining;

LEFT JOIN (MAN_TRAININGS)

LOAD

EMPLOYEE_ID

RESIDENT Career_Learning;

////////////////////////////////

//10. DATA_QUALITY: //

////////////////////////////////

DATA_QUALITY:

LOAD Distinct

EMPLOYEE_ID AS EMPLOYEE_ID_,

EMPLOYEE_NAME AS EMPLOYEE_NAME_,

PRACTICE_CODE AS Practice

RESIDENT Employees;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

ARVE AS ARVE_,

COR AS COR_,

MU_direct AS MU_direct_,

'Data' AS EXISTS_IN_FINANCE_DATA

RESIDENT Financial;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_TRAINING_DATA

RESIDENT Career_Learning;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_MYPATH_DATA

RESIDENT My_Path;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_CV_DATA,

CV_LAST_UPDATE_DATE AS CV_LAST_UPDATE_DATE_

RESIDENT CV_Update;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_TIME_SHEET_DATA

RESIDENT Time_Sheets;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_LEAVE_DATA

RESIDENT Leave;

LEFT JOIN (DATA_QUALITY)

LOAD

EMPLOYEE_ID AS EMPLOYEE_ID_,

'Data' AS EXISTS_IN_CERT_DATA

RESIDENT Certificates;

SUMMARY:

LOAD

Practice,

count(EMPLOYEE_ID_) AS N_EMPLOYEES,

SUM(if(isnull(EXISTS_IN_FINANCE_DATA), 1, 0)) AS N_MISSING_FIN,

SUM(if(isnull(EXISTS_IN_MYPATH_DATA), 1, 0)) AS N_MISSING_MY_PATH,

SUM(if(isnull(EXISTS_IN_TRAINING_DATA), 1, 0)) AS N_MISSING_TRAIN,

SUM(if(isnull(EXISTS_IN_TIME_SHEET_DATA), 1, 0)) AS N_MISSING_TIME_SHEET,

avg(ARVE_) AS avgARVE,

avg(COR_) AS avgCOR,

avg(MU_direct_) AS avgMU_direct,

Median(ARVE_) AS medARVE,

Median(COR_) AS medCOR,

Median(MU_direct_) AS medMU_direct

Resident DATA_QUALITY group by Practice;

Store CV_Update into $(vCSVPath)CV_Update.csv (txt, delimiter is ';');

Store Career_Learning into $(vCSVPath)Career_Learning.csv (txt, delimiter is ';');

Store Financial into $(vCSVPath)Financial.csv (txt, delimiter is ';');

Store Time_Sheets into $(vCSVPath)Time_Sheets.csv (txt, delimiter is ';');

Store My_Path into $(vCSVPath)My_Path.csv (txt, delimiter is ';');

Store MandatoryTraining into $(vCSVPath)MandatoryTraining.csv (txt, delimiter is ';');

Store Certificates into $(vCSVPath)Certificates.csv (txt, delimiter is ';');

Store Employees into $(vCSVPath)Employees.csv (txt, delimiter is ';');

Store

EMPLOYEE_ID,ID,PRACTICE_CODE,EMPLOYEE_NAME,Emailadres

from Employees into $(vExPath)Employees_NL.csv (txt, delimiter is ';');

//Store EMPLOYEE_ID, IND_MYPATH_DATA, MYPATH_ASSIGNMENTS, MYPATH_STATUS_CODE from My_Path into My_Path.csv (txt, delimiter is ';');

Store EMPLOYEE_NAME_, EMPLOYEE_ID_, EXISTS_IN_FINANCE_DATA,

EXISTS_IN_TRAINING_DATA, EXISTS_IN_MYPATH_DATA, EXISTS_IN_CV_DATA, EXISTS_IN_TIME_SHEET_DATA

from DATA_QUALITY into $(vCSVPath)DATA_QUALITY.csv (txt, delimiter is ';');

Report Inappropriate Content · ‎2013-03-22

Hello John,

This needs investigation, so I would like you to follow below steps:

1) Create Listboxes with MAND_TRAINING_NAME, another with EMPLOYEE_ID.

2) Now select that 1 row with incorrect data and see if you get six MAND_TRAINING_NAME associated with that in the list.

You need to break the things into smaller pieces and analyze what be causing that issue.

Thanks,

angad