topic Re: where Years = Year(Now()) fail in QlikView

where Years = Year(Now()) fail

sfloberg — Wed, 25 Nov 2020 16:16:04 GMT

I have a table who looks like this:

LOAD * INLINE [

Days, Years
1, 2015

2, 2015

3, 2015

4, 2016

5, 2016

]

where Years = Year(Now());

The loading of the script fill fail because 2017 isn't in the inline. How can I make the script not fail even if the year isn't in the inline yet?

When I try: where Years = Max(Years);. the script fails too by giving me: Unknown error.

How can i re-write the code so the loading of the script will not fail even if the year isn't in the inline yet?

Re: where Years = Year(Now()) fail

surendraj — Sun, 22 Jan 2017 10:57:16 GMT

Hi,

LOAD * INLINE [

Days, Years

1, 2015

2, 2015

3, 2015

4, 2016

5, 2016

]

where Years =Previous(Years);

Re: where Years = Year(Now()) fail

sfloberg — Sun, 22 Jan 2017 11:01:04 GMT

But this will only give me the 2016 values. I need it to be the 2017 values.

Re: where Years = Year(Now()) fail

sfloberg — Sun, 22 Jan 2017 11:02:49 GMT

So when the admin enter the 2017 values, the code you just wrote will only give me the 2016 values. I need it to give me the 2017 values and the script to load even if just 2016 exists in the file.

Re: where Years = Year(Now()) fail

surendraj — Sun, 22 Jan 2017 11:04:18 GMT

There are no 2017 values.please elaborate on your requirement

Re: where Years = Year(Now()) fail

sfloberg — Sun, 22 Jan 2017 11:17:10 GMT

When the admin enter the 2017 values - I need it to give me the 2017 values. But if only 2016 values exists I want it go give me 2016 and the script to load.

Re: where Years = Year(Now()) fail

vinieme12 — Sun, 22 Jan 2017 11:24:20 GMT

Hi,

You cannot use Aggregation functions in the Where clause. and we do not have the HAVING statement in Qlikview

So you have two options

1) load the temp table >> Then Load Max(Year) in another table and using Peek() , retrieve the value in a variable

then load Resident from the temp table where Years = vVariableCreated;

2) if you have a SQL Select statement use the Having Clause there.!

Re: where Years = Year(Now()) fail

vinieme12 — Sun, 22 Jan 2017 11:27:39 GMT

Example

Tab_MaxYear:

load Max(Years) as MaxYear;

LOAD * INLINE [

Years

2015

2016

];

let vMaxYear = Peek('MaxYear',0,'Tab_MaxYear');

FACT:

LOAD * INLINE [

Days, Years

1, 2015

2, 2015

3, 2015

4, 2016

5, 2016

]

where Years = $(vMaxYear);

Re: where Years = Year(Now()) fail

maxgro — Sun, 22 Jan 2017 11:29:30 GMT

It seems to me you only want the record with the max year

This is one of the option

LOAD * INLINE [

Days, Years

1, 2015

2, 2015

3, 2015

4, 2016

5, 2016

]

;

Right Keep(X)

load max(Years) as Years

Resident X;

Re: where Years = Year(Now()) fail

sfloberg — Sun, 22 Jan 2017 11:38:29 GMT

Hi!
I tried this in a simple app before I try it in the correct one. But I get an error.

Test1_tmp:

load Max(År) as MaxYear;

LOAD * INLINE [

År

2016

2015

];

let vMaxYear = Peek('MaxYear', 0, 'Test1_tmp');

Test1:

LOAD * INLINE [

Dagar, År

1, 2016

2, 2016

3, 2016

4, 2016

5, 2015

]

Where År = $(vMaxYear);

Re: where Years = Year(Now()) fail

surendraj — Sun, 22 Jan 2017 11:59:15 GMT

Try with applying single quotes

Where År = '$(vMaxYear)';

Re: where Years = Year(Now()) fail

swuehl — Sun, 22 Jan 2017 12:20:29 GMT

Stephanie, your script does not fail if I run it. It's just returning an empty table with your two fields.

Are you using some more code?

If no records exists in your loaded table, you can't access any records in subsequent LOAD statements, right?

Is this maybe causing an error in your reload?

Stephanie Floberg wrote:

When the admin enter the 2017 values - I need it to give me the 2017 values. But if only 2016 values exists I want it go give me 2016 and the script to load.

Have you tried what maxgro suggested?

edit: If you don't like the extra table, then you can drop it:

LOAD * INLINE [

Days, Years

1, 2015

2, 2015

3, 2015

4, 2016

5, 2016

]

;

MaxYear:

Right Keep(X)

load max(Years) as Years

Resident X;

Drop Table MaxYear;