topic Re: Missing data in table with traffic lights in QlikView

Missing data in table with traffic lights

Fri, 22 Mar 2013 07:28:46 GMT

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),

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 07:53:53 GMT

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 08:04:29 GMT

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 08:14:36 GMT

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.

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 08:27:57 GMT

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 09:39:04 GMT

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 09:44:02 GMT

Hi,

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

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 09:53:09 GMT

Please find attached screenshot of the link,

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

thanks,

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 09:58:06 GMT

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;

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

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

DROP Table tables;

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

//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

//One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet names located in the workbooks.

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

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

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

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

DROP Table tables;

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

//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 ';');

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 10:35:08 GMT

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

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 10:45:21 GMT

I am not a Qview specialist never touched code before on this. So breaking it into pieces is hard as you don't know the pieces.

First problem is having a good load of data without the ODBC error. I moved Qview to a local drive and the message is the same. The message differs in pointing to another path but to the same relative path.

This is the second time I have problems with ODBC it is a very fragile hook in Qview.

I think the problem is not handeling the situation where there is no training data. An extension of the If statement must be built but how?

In the attachment you can see my listboxes.

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:05:51 GMT

I had found a registry entry pointing to the file what must I do with that entry?

Delete it? Update it but to what file must it point?

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:20:35 GMT

delete it, since we do not need it. Give it a try, it wont effect your qvw.

Angad

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:23:45 GMT

Deleted the entries but still the same error message.

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:26:51 GMT

Give another try,

Test all connections in ODBC.

Try closing the app and Qlikview itself. Restart it and then try loading it. (I know this will not help, but i still ask you to give it a try.)

Rest I think you need ask the qlikview moderator or any other senior member to help you more into this.

Thanks,

angad

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:31:03 GMT

I already quit Qlikview. How can I test ODBC connections. The problem is the value of the string holding the filename. Ask for moderator? How do you ask this?

Did you look at my listboxes I showed you?

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:35:53 GMT

The message comes from this small loop

So file is pointing to files which don't exist in the folder.

FOR Each file in FileList(vDir)

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

tables:

SQLtables;

DISCONNECT;

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 11:52:44 GMT

I debuged the script and come across this:

A loop over the directory is done to get the files in it. But there is a file in the loop what is not on the filesystem.

So what to do now?

John

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 12:39:29 GMT

John,

I have commented this already on your original post :

http://community.qlik.com/message/325319#325319

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

The above statement means that :

1) you should have a system/user DSN for Excel File created with named Excel Files.

2) The Files must be present in the path where its pointing to.

I assume that you must have changed the DocumentName, or Path which has changed the vDocPath value.

LET vDocPath = left(DocumentPath(),Len(DocumentPath())-(12+Len(DocumentName())));

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

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

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

FOR Each file in FileList(vDir)

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

tables:

SQLtables;

DISCONNECT;

Connection String will must finally interpret as :

ODBC CONNECT32 TO [Excel Files;DBQ=Drivename:\FolderName\SubFolder\FileName.xls];

If you do not have files in the file system, you should inform this to your Lead.

Thanks,

angad

Re: Missing data in table with traffic lights

Fri, 22 Mar 2013 12:44:19 GMT

I had a look at the listboxes, and it seems that for the Employee selected it has no Mandatory Training records available, so it will give Blank, as in your first post.

Try using Mandatory Field Name as one of your dimensions.

thanks

angad