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 · Accepted solution]

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

[#3]

John,

If you have correct named files in the path pointing at the ODBC connections, then it will work fine.

To test connection :

Go to ODBC settings, click configure on your "Excel Files" named ODBC connection, then click on select workbook button,

On that left hand side, you must see all the files with required names.

So if your script is trying to point to d:\Folder\Abc.xlsx, then you must see Abc.xlsx file on left hand side.

Thanks,

angad

[#4]

When I click configure I got an ODBC error message.

John

[#5]

Oh ok, you need  to delete that connection and make a new one.

[#6]

No not a specific one I got an error when clicking on configure. Can we do something like skype screen sharing?

[#7]

John, I am sorry, that would not be possible since I use official id on skype.

Though about ODBC error, you can google it, I suspect that might be due to deletion of the registry file, which we did before.

I would suggest deleting the connection and recreating it.

angad

[#8]

But how to recreate it?

So the deletion in the registry was risky stuf now I am more far from the solution 😞

The messag is the driver could not be found. I run MDAC again may be it helps.

John

[#9]

I have mdac_typ.exe when I click on it nothing happens it looks like extraction is done. But where is the extraction located?

[#10]

John,

To create a new connection. Follow below steps:

1) Open Qlikview edit script -> Tools -> ODBC 32

2) You can create System or User DSN, by clicking on Add.

3)  List of drivers come, Select Driver do Microsoft Excel(*.xls), click finish

4) Add a Data Source Name: "Excel Files"

Thats it.

angad

[#11]

I did made a new entry without using a workbook since that is done in the qview app. Still he is complaining about the same file Grrrrrrr

When I point it to a workbook the message is the same. I can't find it anymore in my registry. So where is the file name coming from. Not on my disc not in the registry

John