Missing data in table with traffic lights

[#1]

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

[#2]

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

[#3]

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

[#4]

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

Angad

[#5]

Deleted the entries but still the same error message.

John

[#6]

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

[#7]

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

[#8]

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;

NEXT

John

[#9]

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

[#10]

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;

NEXT

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

[#11]

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