Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eduardo_dimperio
Specialist II
Specialist II

Using Exist

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

19 Replies
swuehl
MVP
MVP

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

ArnadoSandoval
Specialist II
Specialist II

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

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
eduardo_dimperio
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

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

swuehl
MVP
MVP

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
Specialist II
Specialist II
Author

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
MVP
MVP

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
Specialist II
Specialist II
Author

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
MVP
MVP

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;