topic Re: EXCEL import data filtering in QlikView

EXCEL import data filtering

Mon, 04 Aug 2014 13:48:40 GMT

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

Re: EXCEL import data filtering

Mon, 04 Aug 2014 13:57:40 GMT

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

Re: EXCEL import data filtering

Mon, 04 Aug 2014 13:59:09 GMT

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.

Re: EXCEL import data filtering

Mon, 04 Aug 2014 14:10:04 GMT

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

Re: EXCEL import data filtering

simenkg — Mon, 04 Aug 2014 14:14:07 GMT

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

Re: EXCEL import data filtering

Mon, 04 Aug 2014 14:26:50 GMT

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

Re: EXCEL import data filtering

simenkg — Mon, 04 Aug 2014 14:28:31 GMT

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.

Re: EXCEL import data filtering

Mon, 04 Aug 2014 14:38:16 GMT

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

Re: EXCEL import data filtering

simenkg — Mon, 04 Aug 2014 14:40:12 GMT

Specify the name of the table like this:

qryUzavreteObdobie:

SQL SELECT UZAVRENEOBDOBI

FROM "rsv2_stav".dbo."qryUzavreteObdobie";

Re: EXCEL import data filtering

Mon, 04 Aug 2014 14:56:18 GMT

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

Re: EXCEL import data filtering

simenkg — Mon, 04 Aug 2014 14:57:46 GMT

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

Re: EXCEL import data filtering

Mon, 04 Aug 2014 15:01:53 GMT

This would return data for 1.6.2014 upwards.

But I need only data for 2.6.2014 upwards.

Duski

Re: EXCEL import data filtering

simenkg — Mon, 04 Aug 2014 15:04:30 GMT

Ok.

You are reading the first table twice.

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');

Re: EXCEL import data filtering

Mon, 04 Aug 2014 18:00:29 GMT

Hi, Simen,

this worked

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

Thank you very much.

Duski