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

[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

[swuehl]

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

[swuehl · Accepted solution]

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.

[eduardo_dimperio]

Yeah, i think the best way to my reality will be only load the field values and check the date after. Sad, but ok.

Thank you Stefan for all the explanation.

[ArnadoSandoval]

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

[eduardo_dimperio]

Hi Arnaldo, thank you for your time to help me, it was helpfull as well Stefan's help.

I didn't quite understand your question, but i presume that you want to know what's my input, right?

If so, imagine that im get measures every hour but not precisely by hour, so i can have measures 00:05, 1:21, 2:45, 3:24 etc. But only one measure by hour, so i get the hour using HOUR() and Floor just to get easier to compare.

It's answer your question my friend?

[ArnadoSandoval]

Hi Eduardo,

You got my question, based on the times you posted, does it mean that your process run every day at 00:05, 1:21, 2:45, 3:25, etc. If the HOUR and FLOOR guarantee that any measures taken between 2:00 and 2:59 will appear as taken at 2:00, then the EXIST is an option for your filtering.

[eduardo_dimperio]

Hey my friend,

That was my thought but, for some reason doesn't work, when i use EXIST() my query return empty

[ArnadoSandoval]

Hi Eduardo,

I will get back to you, as I have date that I could use to simulate your challenge, I will work on it over the weekend.

Regards,

[eduardo_dimperio]

Arnaldo Thank you so much for your time !