Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
eduardo_dimperi
Valued Contributor 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
MVP
MVP

Re: Using Exist

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.

19 Replies
MVP
MVP

Re: Using Exist

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

tinaco110
Contributor III

Re: Using Exist

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_dimperi
Valued Contributor II

Re: Using Exist

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_dimperi
Valued Contributor II

Re: Using Exist

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

MVP
MVP

Re: Using Exist

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_dimperi
Valued Contributor II

Re: Using Exist

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.

MVP
MVP

Re: Using Exist

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_dimperi
Valued Contributor II

Re: Using Exist

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?

MVP
MVP

Re: Using Exist

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;