3 Replies Latest reply on Nov 11, 2014 1:42 PM by Gregor

    Excel VLOOKUP on dates returned by PI Datalink

    yves.lesperance
      Hi ! I have many excel reports that uses PI Sampled Data and PI Compressed Date. These reports uses a date generated with Excel and a VLOOKUP to go through the returned values.   For some reasons, this stopped working on November 10th at 00:00.  It looks like Excel has trouble comparing dates generated in it and returned from PI Datalink.  Excel always had trouble with floating point.  However, what I cannot explain, is why it always worked before November 10th at 00h00.  Now it works 1/3 of the time, which is on the hours where it needs only 3 decimals.  Anyone else have that problem ? What is the solution?  I prefer solution where I don't have to modify all the excel reports everyone built... I tried attaching a test file with Chrome and IE and both failed. I'll try later. For those willing to test anyway and see for themselves : I generate the dates by putting 2014-11-09 00:00 in Cell A1.  Cell A2 contains =(A1*24+1)/24 and I drag down to generate two days of timestamps.  I get some values from PI using an hourly PI Sampled date for the same time span.  Note that dates returned at least appear to be the same.  Then I do =VLOOKUP(A1;$B$1:$C48;2;FALSE) in D1 and drag down. and it works on and off since today...
        • Re: Excel VLOOKUP on dates returned by PI Datalink

          Hello Yves,

           

          If I understand correctly you suspect the issue to start with a particular date within the report, so an issue depending on the query date or better said starting with the 10-November 2014 at midnight as query date. The issue did not start at a certain date, right? So, you don't suspect any change on the client is causing this behavior. Is this right?

           

          You are using Excel function VLOOKUP to compare query timestamps against those returned by PI DataLink. If you say that it is working "on and off", you mean sometimes you get the expected result but sometimes you don't, right? Can you please check the precision of timestamps by including seconds and milliseconds (format yyyy-mm-dd hh:mm:ss.000)?

           

          I've tried reproducing your observation. You've started describing very detailed what cell contains what information but then you lost me. I am thinking about assigning a Technical Support engineer because it might be easier getting an understanding of what happens in a remote session.

           

          Just to be sure, please check with 'Programs and Features' dialog if something was recently updated (you can sort by date by clicking the date column).

            • Re: Excel VLOOKUP on dates returned by PI Datalink
              yves.lesperance

              Hi! For some reasons I still cannot attach the file ( it says the url is not found even if I'm uploading the file successfully ).

               

               

               

              And the text is not formatted clearly when I see the post even though it's fine in the preview pane.

               

               

               

              So I'll open a ticket to the tech support and see with them. If you can see it formatted well enough, here was my original post :

               

               

               

              Here is how I could show how it works. My calculated excel date formatted to the ms. My PI Sampled date and values, also formatted to the ms. They are the same.

               

              The VLookup function is the same and works for every day before Nov 10th.

               

               

               

              As you can see in the data I pasted, it starts giving N/A from this point. This was repeatable with another PI Server and multiple machines.

               

               

              Excel Date PI SAMPLED DATE PI VALUES VLOOKUP
              2014-11-09 00:00:00.000 2014-11-09 00:00:00.000 101.288002 101.288002
              2014-11-09 01:00:00.000 2014-11-09 01:00:00.000 101.2900009 101.2900009
              2014-11-09 02:00:00.000 2014-11-09 02:00:00.000 101.2870026 101.2870026
              2014-11-09 03:00:00.000 2014-11-09 03:00:00.000 101.2890015 101.2890015
              2014-11-09 04:00:00.000 2014-11-09 04:00:00.000 101.2850037 101.2850037
              2014-11-09 05:00:00.000 2014-11-09 05:00:00.000 101.2890015 101.2890015
              2014-11-09 06:00:00.000 2014-11-09 06:00:00.000 101.288002 101.288002
              2014-11-09 07:00:00.000 2014-11-09 07:00:00.000 101.2910004 101.2910004
              2014-11-09 08:00:00.000 2014-11-09 08:00:00.000 101.2929993 101.2929993
              2014-11-09 09:00:00.000 2014-11-09 09:00:00.000 101.2959976 101.2959976
              2014-11-09 10:00:00.000 2014-11-09 10:00:00.000 101.2939987 101.2939987
              2014-11-09 11:00:00.000 2014-11-09 11:00:00.000 101.3000031 101.3000031
              2014-11-09 12:00:00.000 2014-11-09 12:00:00.000 101.3000031 101.3000031
              2014-11-09 13:00:00.000 2014-11-09 13:00:00.000 101.2919998 101.2919998
              2014-11-09 14:00:00.000 2014-11-09 14:00:00.000 101.2990036 101.2990036
              2014-11-09 15:00:00.000 2014-11-09 15:00:00.000 101.2979965 101.2979965
              2014-11-09 16:00:00.000 2014-11-09 16:00:00.000 101.2949982 101.2949982
              2014-11-09 17:00:00.000 2014-11-09 17:00:00.000 101.2900009 101.2900009
              2014-11-09 18:00:00.000 2014-11-09 18:00:00.000 101.2910004 101.2910004
              2014-11-09 19:00:00.000 2014-11-09 19:00:00.000 101.2860031 101.2860031
              2014-11-09 20:00:00.000 2014-11-09 20:00:00.000 101.2809982 101.2809982
              2014-11-09 21:00:00.000 2014-11-09 21:00:00.000 101.2789993 101.2789993
              2014-11-09 22:00:00.000 2014-11-09 22:00:00.000 101.2750015 101.2750015
              2014-11-09 23:00:00.000 2014-11-09 23:00:00.000 101.2730026 101.2730026
              2014-11-10 00:00:00.000 2014-11-10 00:00:00.000 101.2720032 101.2720032
              2014-11-10 01:00:00.000 2014-11-10 01:00:00.000 101.2710037 #N/A
              2014-11-10 02:00:00.000 2014-11-10 02:00:00.000 101.2699966 #N/A
              2014-11-10 03:00:00.000 2014-11-10 03:00:00.000 101.276001 101.276001
              2014-11-10 04:00:00.000 2014-11-10 04:00:00.000 101.2770004 #N/A
              2014-11-10 05:00:00.000 2014-11-10 05:00:00.000 101.2809982 #N/A
              2014-11-10 06:00:00.000 2014-11-10 06:00:00.000 101.2819977 101.2819977
              2014-11-10 07:00:00.000 2014-11-10 07:00:00.000 101.288002 #N/A
              2014-11-10 08:00:00.000 2014-11-10 08:00:00.000 101.2929993 #N/A
              2014-11-10 09:00:00.000 2014-11-10 09:00:00.000 101.2900009 101.2900009
              2014-11-10 10:00:00.000 2014-11-10 10:00:00.000 101.2949982 #N/A
              2014-11-10 11:00:00.000 2014-11-10 11:00:00.000 101.2860031 #N/A
              2014-11-10 12:00:00.000 2014-11-10 12:00:00.000 101.2900009 101.2900009
              2014-11-10 13:00:00.000 2014-11-10 13:00:00.000 101.288002 #N/A
              2014-11-10 14:00:00.000 2014-11-10 14:00:00.000 101.2809982 #N/A
              2014-11-10 15:00:00.000 2014-11-10 15:00:00.000 101.2839966 101.2839966
              2014-11-10 16:00:00.000 2014-11-10 16:00:00.000 101.2789993 #N/A
              2014-11-10 17:00:00.000 2014-11-10 17:00:00.000 101.276001 #N/A
              2014-11-10 18:00:00.000 2014-11-10 18:00:00.000 101.2740021 101.2740021
              2014-11-10 19:00:00.000 2014-11-10 19:00:00.000 101.2730026 #N/A
              2014-11-10 20:00:00.000 2014-11-10 20:00:00.000 101.2750015 #N/A
              2014-11-10 21:00:00.000 2014-11-10 21:00:00.000 101.2720032 101.2720032
              2014-11-10 22:00:00.000 2014-11-10 22:00:00.000 101.2710037 #N/A
                • Re: Excel VLOOKUP on dates returned by PI Datalink

                  Hello Yves,

                   

                  The colleagues from Technical Support are the right ones to address issues like this.

                   

                  My understanding is that VLOOKUP returns #N/A in case it cannot locate the date contained in column B with any date in column (range) A. Even with the sub seconds, dates appear to be identical. To me this behavior is a secret too. I would try different data formats to identify the differences.

                   

                  As far as I can see content in columns A and B and in C and D is supposed to be the same. I feel a bit stupid to ask but what's the purpose of using VLOOKUP function?