Exits with more conditions

[jasmina_karcic]

Hi experts,

Can you help me about using exists function?

How to use in better way something like:

"WHERE exists(ID_NUMBER) AND DATE>'31.12.2019'?

This make load very slowly...

Jasmina

[Kushal_Chawda]

May be you can create list of Date values which you need to apply as a filter and use it in exists 

Date:
Load date(makedate(2019,12,31) + iterno()-1,'DD.MM.YYYY') as DATE
autogenerate 1
while makedate(2019,12,31) + iterno()-1 <= makedate(2020,12,31);

Note: change the highlighted to make sure that you create the Dates till when you require.  Also, this load statement should be before your actual data load.

Now you can use the condition like below

"WHERE exists(ID_NUMBER) AND exists(DATE)?

[marcus_sommer]

You could try to use two loadings like:

dummy: load date(floor(yearstart(today) + recno() - 1)) as DATE autogenerate daynumberofyears(today());

t1: load ... from ... where exists(DATE);
t2: noconcatenate load ... resident t1 where exists(ID_NUMBER);

You may need further to adjust your load-order and/or to rename some of your fields to ensure that your fields within the exists() contain exactly your wanted values. You may also store/drop the intermediated step and loading the second one from a qvd, too.

The only alternatively seems to be to concat both fields in beforehand so that it would be just a single parameter-field which is queried in the exists().

- Marcus

[jasmina_karcic]

Sounds good, I must try, I will try explain better

This application is like a financial report. I am watching 2018, 2019, and 2020 year, for every each date in these years.

Ehh I made qvd for 2018, qvd for 2019, but last qvd look last 13 months (I don't need all, because I will get duplicate data, I just need from 1.1.2020).

I want load 2018, on this table concatenate 2019, both from qvd that I dont reload anymore, because old data. 

From 1.1.2020 to now also i concatenate to these tables up, and i must restrict with date>31.12.2019. 

It would be all okay If I don't need restrict also ID_NUMBER.

😕

Thanks

[marcus_sommer]

I think I would try to ensure that my qvd-sources for this task are on a clean yearly-level - it may cost you an additional step in beforehand if you couldn't change the logic which originally creates them.

- Marcus

[Kushal_Chawda]

As @marcus_sommer  already suggested that you need to change the underlying logic of creating QVDS which should generate the QVDs with dates that belongs to particular year.

But for time being, you can do something like below

Date:
Load date(makedate(2020,1,1) + iterno()-1,'DD.MM.YYYY') as DATE
autogenerate 1
while makedate(2019,12,31) + iterno()-1 <= makedate(2020,12,31);

Data:

load *
FROM [Data_2020.Qvd](Qvd)
where exists (DATE);

concatenate(Data)
load *
FROM [Data_2019.Qvd](Qvd);

concatenate(Data)
load *
FROM [Data_2018.Qvd](Qvd);

Note: In exists I just put condition of DATE as you mentioned is it ok if you don't have it for ID_NUMBER.