19 Replies Latest reply: Mar 24, 2017 8:14 AM by Eduardo DImperio RSS

    Using Exist

    Eduardo DImperio

      Hi everyone !

       

      I have 3 qvd and i want to check if i have records in a date, to be more specific 2 am and 5 am. So i wrote this code to check and if the record doesnt exist i will discard. But i receive the message unknow error.

       

      Help Please

       

      Let [vPath]='MI';

       

       

        for a=0 to 2

       

        let vCarga=Timestamp(Today()-$(a),'YYYYMMDD');

       

        [CONCAT_TABLE_$(vPath)]:

        LOAD

        Distinct

        *

        FROM [lib://Dados/$(vPath)/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd) where DATE_READ>TODAY()-2

          AND Exists(Floor( Time(frac(DATE_READ))),2);

       

        next

        • Re: Using Exist
          Stefan Wühl

          Not really sure what you want to achieve, but the first argument to Exists() functions need to be a field name.

            • Re: Using Exist
              Eduardo DImperio

              Hi Stefan, sorry i think that i need improve a little more my english.

              I want load only the QVDs that have hour equal 2 am.

               

              So i did this to get  HOUR(Time(frac(DATE_READ))) and after that a where clause

               

              where Exists(DATE_READ_TIME,2);

            • Re: Using Exist
              Arnaldo Sandoval

              Hi Eduardo,

               

              My understanding of the function Exists is: It finds exact match of a value within the data being loaded, in your example, the expression Floor( Time(frac(DATE_READ))) returned value will be taken as the column name in the data your are loading to match (or be equal to) 2 (the second parameter.

               

              Based in your example, the data your are loading does not have a "DATE_READ_TIME" column, I suggest to create one, like this:

              ===

              Let [vPath]='MI';

                for a=0 to 2

                   let vCarga=Timestamp(Today()-$(a),'YYYYMMDD');

               

                   [CONCAT_TABLE_$(vPath)]:

                   LOAD

                   Distinct

                       *,

                       Floor( Time(frac(DATE_READ))) as DATE_READ_TIME

                   FROM [lib://Dados/$(vPath)/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd)

                   where DATE_READ>TODAY()-2

                   AND Exists( DATE_READ_TIME, 2);

                next

              ===

              Hope this helps

                • Re: Using Exist
                  Eduardo DImperio

                  Arnaldo you are completely right, and your help works very weel, but im still dont get my output.

                   

                  If i dont use the Exists() expression i found some records with 2 am value (value that i want)

                  but if i use the Exists() my qvd load returns 0 lines.

                   

                  Where im wrong please?

                   

                    [CONCAT_TABLE_$(vPath)]:

                    LOAD

                    Distinct

                    *,

                     HOUR(Time(frac(DATE_READ))) as DATE_READ_TIME

                    FROM [lib://Dados/$(vPath)/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd); //where  Exists(DATE_READ_TIME,2);

                   

                  Exists1.JPG

                   

                   

                  Exists2.JPG

                   

                   

                   

                    [CONCAT_TABLE_$(vPath)]:

                    LOAD

                    Distinct

                    *,

                     HOUR(Time(frac(DATE_READ))) as DATE_READ_TIME

                    FROM [lib://Dados/$(vPath)/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd) where Exists(DATE_READ_TIME,2);

                   

                   

                  Exists3.JPG

                    • Re: Using Exist
                      Stefan Wühl

                      If your input table does not contain the hour field, I think you can't use the Exists() function here.

                       

                      Use a WHERE condition like

                       

                      LOAD ....

                      FROM .....

                      WHERE Hour(DATE_READ) = 2;

                        • Re: Using Exist
                          Eduardo DImperio

                          Hi Stefan,

                           

                          But i created a Hour Field named DATE_READ_TIME and it returns the data that i looking for, but for some reason when i use Exists return no data.

                           

                          I need to use Exists because i dont want only data with Hour 2 am, i need the qvd that have this data inside.

                            • Re: Using Exist
                              Stefan Wühl

                              Not sure I understand what you want to do, but Exists() checks values within a symbol table, that means you need to load field values which you then can check for existence during a subsequent record load.

                                • Re: Using Exist
                                  Eduardo DImperio

                                  Hi Stefan, ill try explain better and thank you for your time.

                                   

                                  I have some meters in my company and it get values every hour. I need to check this values between 2am and 5 am

                                  So i create a qvd by day with this values and the date of the measuring.

                                  But for some reason sometimes the meters doesnt work and dont bring some data, so in this case with i have no data between 2 and 5 am i need to discard or not load this qvd.

                                  For that reason i thought to use Exists() and check if i have or not values between this hours in my qvds.

                                  undestanding my problem now?

                                    • Re: Using Exist
                                      Stefan Wühl

                                      So if a QVD shows records within the time frame, you don't want to load only these records, but all records for that day?

                                      And if a QVD shows no records within the time frame, you don't want to load any records from that QVD?

                                       

                                      I think Exists() won't help you here, unless you prepare a field when creating the QVD that can be used to check your condition. For example, of your table of meter records for a date shows records within the timeframe, join a flag field to all records with value 1, else 0.

                                       

                                      Now you can use something like

                                       

                                      TEMP:

                                      LOAD * INLINE [

                                      FlagField

                                      1

                                      ];

                                       

                                      LOAD * FROM Meter.qvd (qvd)

                                      WHERE EXISTS(FlagField);

                                       

                                      DROP TABLE TEMP;

                                      DROP FIELD FlagField;

                                        • Re: Using Exist
                                          Eduardo DImperio

                                          Yes, exactly, if a QVD shows no records within the time frame i want to skip this qvd.

                                          But why Exists() doesnt apply? I thought that function would check if i have this record inside a qvd and if no, would skip it

                                            • Re: Using Exist
                                              Stefan Wühl

                                              Exists() does basically check against symbols of your resident data model (field values that already has been loaded into RAM).

                                                • Re: Using Exist
                                                  Stefan Wühl

                                                  If you need to check always the same time frame, it would probably be easiest to add a flag to the file name when writing the QVD. Now you can decide just by looking at the filename if you want to load the QVD or not.

                                                   

                                                  Another option would be to only load the field values of your timestamp field, check for values within the time frame, then load all other data if check passes.

                                                   

                                                  Or load all data of all QVDs, then remove records for dates without records within timeframe in a second step.

                                                   

                                                  Which approach is best might depend on the number of records you are coping with and if you need to check the QVD in different load scripts, and potentially for different time frames.

                                    • Re: Using Exist
                                      Arnaldo Sandoval

                                      Hi Eduardo,

                                       

                                      Aside to the help Stefan gave you resolved your problem, now regarding implementing the EXISTS function, it seems you were comparing a time column (DATE_READ_TIME) against the integer 2,  I would have formatted the 2 to hours, something like, and still, I will look at the values returned by the DATE_READ_TIME, are they always 2 for any record arriving between 2:00 and 2:59 AM?

                                       

                                      Exists(DATE_READ_TIME, HOUR(2));


                                      hth