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

[swuehl]

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

[ArnadoSandoval]

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

[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);

[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);

  [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);

[swuehl]

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;

[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.

[swuehl]

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.

[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?

[swuehl]

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;