topic Re: load only specific years from gvd-file in QlikView

load only specific years from gvd-file

jakobjosef — Fri, 27 Jul 2018 09:58:26 GMT

Good day dear QlikView community,

I have already learned a lot from this forum, now it's time for my first question, because unfortunately I have not found a suitable answer.

Following problem:

I load data from a QVD file, more specifically planning data for the presence of employees. The script always uses the complete data set (STARTING WITH YEAR 2007), but I only need the data starting with 2017 (--> year 2017 and 2018).

Each line has a posting date and is loaded as follows:

date ([date (pzdnkal.date)]) as the posting-date,

[pnr (pzdnkal.pnr)] as person number,

etc.....

FROM

$ (V21_Datamodel_QVD) DimPersPlanungsdaten.qvd (QVD)

fyi: The data is outputed in the format dd.mm.yyyy, i.e. 26.05.2017.

My question:

Which command should I give after this line (... the posting date) so that only the years 2017 and 2018 will be loaded?

If further information is needed I can indicate this.

Thank you very much and best regards from Vienna,

Jakob

Additive:

Same problem I have with another database. Here, however, the values are always combined with the time "00:00:00", e.g. a value is given as "26.05.2009 00:00:00".

Again, I only want values starting from 2017 (--> so 2017 & 2018), and without the clock addition, since it is always "00:00:00" anyway.

Thank you very much!

Re: load only specific years from gvd-file

felipedl — Mon, 30 Jul 2018 18:24:22 GMT

Hi Jakob,

Since you said you have a date type field on your QVD, you could simply do something lik:

QVDLoad:

Load *

From [QVDName.qvd] (qvd)

where year(pzdnkal.date)>=2017;

That is, assuming your date field is interpreted as a number on qlikview (42000 style number, like Excel date format).

This will only load specific records, won't be a optimized QVD load and will only get the dates that have the year correspondence greater than 2017 (so giving you 2017 and 2018).

For the second database, you can use the floor() statement to get the integer part of the timestamp field you need.]

Like so:

QVDLoad:

Load *

From [QVDName.qvd] (qvd)

where Year(floor([Date Field]))>=2017

Re: load only specific years from gvd-file

jakobjosef — Tue, 31 Jul 2018 05:54:45 GMT

Dear Felip,

thank you very much, both worked pretty well .

There is one last question concerning this topic:

When i load like this, I get the years 2017, 2018 AND 2019, but I only need 2017 & 2018.

I tried some scripting, but it did not work. I tried like:

"where year (pzdnkal.date)>=2017 & <=2019"

"where year (pzdnkal.date) =2017 + 2018"

"where year (pzdnkal.date) =2017 & 2018"

Is there a command for this issue as well?

Thank you very much,

Jakob

Re: load only specific years from gvd-file

felipedl — Tue, 31 Jul 2018 16:46:47 GMT

First one was almost correct

"where year (pzdnkal.date)>=2017 & <2019"

Since you used <= it gets the year 2019 also

"where year (pzdnkal.date)>=2017 & <=2018"

Re: load only specific years from gvd-file

jakobjosef — Wed, 01 Aug 2018 05:28:38 GMT

Thank you Felip.

Unfortunately this solution doesn't work in my case.

Here is my skript:

where year (([datum (pzdnkal.datum)]))>=2017 & <2019

If i run the script, it tells me:

field "a" not found.

I think it means the"&".

Do you know how to deal with it?

Re: load only specific years from gvd-file

tamilarasu — Wed, 01 Aug 2018 06:15:46 GMT

Hi Jacob,

You can try,

Where Year ([datum (pzdnkal.datum)])>= 2017 And Year ([datum (pzdnkal.datum)]) <= 2018;

Where WildMatch(Year ([datum (pzdnkal.datum)]), '2017', '2018');

Re: load only specific years from gvd-file

jakobjosef — Wed, 01 Aug 2018 06:20:34 GMT

Yes this works fine,

thank you Tamil!

Re: load only specific years from gvd-file

BalaBhaskar_Qlik — Wed, 01 Aug 2018 06:22:45 GMT

where Year(floor([Date Field]))>=2017 and Year(floor([Date Field]))<2019;