EXCEL import data filtering

[#1]

Hi, everybody.

I have a QV project with data import from EXCEL via script.

I use the load statement like this and it works fine:

LOAD ....., OBDOBIE, ....

FROM

[source file]

(ooxml, embedded labels)

where OBDOBIE > Date#('1.6.2014','DD.MM.YYYY');

I'd like to improve automation of this statement, because the value for condition > '1.6.2014' I can get from another statement in the same script, connected to a SQL database:

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

This statement works fine too.

I'd like to use the result of Max(UZAVRENEOBDOBI), which is  '1.6.2014 0:00:00', for my load statement.

How can I do this ?

Thanx in advance.

Duski

[#2]

Hi Dusan,

I'd simply right this value to a variable using SET or LET and then do the following:

LOAD ....., OBDOBIE, ....

FROM

[source file]

(ooxml, embedded labels)

where OBDOBIE > vVariable;

Regards,
Marius

[#3]

Hi,

When you load the SQL Table order by OBDOBI Descending.

Then use the PEEK function (which can return the first loaded value which in the aboove case will be the MAX value of OBDOBI ) to store it in a variable.

After this use the variable in the excel condition.

[#4]

Thank you Marius.

Use of variable was the first thing I did, but without success 😞

This is what I tried:

After the SQL-statemet I used:

set vUzavreneObdobi = max(UZAVRENEOBDOBI);

And in the load statemet from EXCEL I used:

where Obdobie > Date#($(vUzavreneObdobi),'DD.MM.YYYY');

No success, script error:

Field not found - <UZAVRENEOBDOBI>

Any suggestion ? Maybe wrong syntax ?

Thanx in advance.

Duski

[simenkg]

You cannot declare a variable like this.

This is how you should do this.

tempMax:

Load max(UZAVRENEOBDOBI) as maxUzavreneObdobi

resident qryUzavreteObdobie;

let vUzavreneObdobi = peek('maxUzavreneObdobi',0,'tempMax');

drop table tempMax;

Then you can use the variable in your load

where Obdobie > Date#('$(vUzavreneObdobi)','DD.MM.YYYY');

Hope this helps.

Regards
SKG

[#6]

Thank you Simen.

Where should I put tempMax statements - before or after the SQL statement

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

Thanx in advance.

Duski

[simenkg]

After.

The Resident statement reads from a table in memory, so it has to be read beforehand. If your table is named something else then you would have to change that statement.

[#8]

Thank you Simen.

I used this:

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

tempMax:

Load max(UZAVRENEOBDOBI) as maxUzavreneObdobi

resident qryUzavreteObdobie;

let vUzavreneObdobi = peek('maxUzavreneObdobi',0,'tempMax');

drop table tempMax;

.....

LOAD ...., Obdobie, ....

FROM

[source file]

(ooxml, embedded labels)

where Obdobie > Date#('$(vUzavreneObdobi)','DD.MM.YYYY');

No data 😞

Thanx in advance.

Duski

[simenkg · Accepted solution]

Specify the name of the table like this:

qryUzavreteObdobie:

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

[#10]

Thank you Simen,

but I'm very sorry, no data 😞 😞 😞

But no error too 🙂

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

qryUzavreteObdobie:

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

tempMax:

Load max(UZAVRENEOBDOBI) as maxUzavreneObdobi

resident qryUzavreteObdobie;

let vUzavreneObdobi = peek('maxUzavreneObdobi',0,'tempMax');

drop table tempMax;

....

LOAD ...., Obdobie, ....

FROM

[\\hxdata\users\vavra\Hornex\Riadenie\Podpora IS_IT\Koncepcie\Data IS\QlikView\20140704_PP.xlsx]

(ooxml, embedded labels)

where Obdobie > Date#('$(vUzavreneObdobi)','DD.MM.YYYY');

Duski