topic Re: QVW load in QlikView

QVW load

Mon, 12 Jan 2015 16:13:01 GMT

Good morning -

I am extremely new to QV and have what is probably a basic question.

My company's BI group creates a refreshed .qvw file everynight and posts it to a shared location.

I then open QV on my local machine and run a binary script to load that .qvw file Binary [\\path\filename.qvw];

Is there a way to modify the binary script so i can limit the fields that are loaded? I really only need to use about half of the available records. Because i am using my local machine the reduced size would be beneficial.

Thank you.

Re: QVW load

Anonymous — Mon, 12 Jan 2015 16:21:17 GMT

try put write

where condition under binary statment like

where sales>=10000;

Re: QVW load

Anonymous — Mon, 12 Jan 2015 16:21:50 GMT

Hi.

I believe that it isn't possible to do a "partial" binary load.

One thing that you can do is after the binary load, use "Drop Table" or "Drop Field" to eliminate the data that you don't need.

Hope it helps.

Regards,

Gabriel

Re: QVW load

Mon, 12 Jan 2015 16:30:45 GMT

Hi,

after the binary load you can manipulate all the present tables in your load script so indeed drop fields, tables etc..

hth

Re: QVW load

Mon, 12 Jan 2015 17:18:59 GMT

Thank you for the prompt response. In this case, i need to keep the tables and fields intact; however, i need to remove about 65% of the records. Is there an equivalent "DROP WHERE" clause? I'm hoping the solution can be part of the script i run each morning.

Re: QVW load

Clever_Anjos — Mon, 12 Jan 2015 17:24:06 GMT

Binary load is an "all or nothing" feature. All your data will be loaded.

You can do an aditional scripting to get rid of data you don´t want

Binary [\\path\filename.qvw];

noconcatenate load * resident T1 where Month='Jan' // just an example

drop table T1

Re: QVW load

Anonymous — Mon, 12 Jan 2015 17:32:08 GMT

You can not only drop tables and fields, but reduce data as well after the binary load. For example:

tmp:

INNER KEEP (TableName) LOAD * // or just one key field instead of *

RESIDENT TableName

WHERE <any conditions you want>;

DROP TABLE tmp;

Re: QVW load

isorinrusu — Mon, 12 Jan 2015 17:39:03 GMT

Subscribing to what Clever Anjos says, you could also use the FIRST prefix when doing further scripting.

Like this:

FIRST 10000 LOAD * RESIDENT MyTable;

Re: QVW load

Mon, 12 Jan 2015 17:41:30 GMT

Wonderful. I was unaware of the Resident option. So if the .qvw loaded is called "MAIN" and i only want the january records it would look like:

tmp:

INNER KEEP (MAIN) LOAD *

RESIDENT MAIN

WHERE date="Jan"

DROP TABLE tmp

And from there i would continue to work within the "MAIN" table, correct?

Re: QVW load

Clever_Anjos — Mon, 12 Jan 2015 17:48:36 GMT

Yes, it´s correct

Re: QVW load

maxgro — Mon, 12 Jan 2015 17:49:37 GMT

you can rename the tables and load with a where condition

Binary [prova export.qvw];

...................

// test comes from binary load

rename Table test to test2;

test:

NoConcatenate

load *

resident test2

where ........somefilter........;

drop table test2;

Re: QVW load

Mon, 12 Jan 2015 19:02:48 GMT

This forum has been extremely helpful. Thank you to all that have pitched in here.

So one last question:

What if the .qvw file i am loading via the binary script is comprised of several tables. Do i edit the script to load each table individually? Or is there a way to reference the entire structure in the LOAD statement?

Re: QVW load

Anonymous — Mon, 12 Jan 2015 19:06:13 GMT

Each table individually...

Re: QVW load

engishfaque — Mon, 12 Jan 2015 19:43:57 GMT

Dear Adam,

This way you can load limited number of records,

LET vFirst = 1000;

TableName:

First $(vFirst) LOAD * from table1.xls;

Kind regards,

Ishfaque Ahmed

Re: QVW load

Tue, 13 Jan 2015 16:56:16 GMT

Good morning, everyone -

I attempted the following script and unfortunately it did not appear to work. The routine fetched several million records but when i opened a field in QV there was no data, only a structure. Any ideas on what i did wrong? I am trying to only import records where the %gencampus_id =1. Table structure attached.

Binary [\\path\local_file.qvw];

tmp:

INNER KEEP (FCTLEADACTIVITY) LOAD *

RESIDENT FCTLEADACTIVITY

WHERE %gencampus_id = 1;

DROP TABLE tmp;

Re: QVW load

Anonymous — Tue, 13 Jan 2015 17:11:57 GMT

Don't use *, it wasn't a good idea - use one field, maybe %ss_id (you know better what field to use)

tmp:

INNER KEEP (FCTLEADACTIVITY) LOAD %ss_id

RESIDENT FCTLEADACTIVITY

WHERE %gencampus_id = 1;

DROP TABLE tmp;

Re: QVW load

Tue, 13 Jan 2015 20:04:53 GMT

Thank you, Michael. That worked!

Re: QVW load

Wed, 14 Jan 2015 17:07:27 GMT

Okay, 1 final question and i can hopefully put this to bed.

I have prepared the following script to reduce my 3 tables based on the given criteria. 2 are working. The DIMSTUDENTNEW table is not working correctly. It is supposed to only load the records where %genmksystudnet_id exists in the FCTLEADACTIVITY table. Right now it is not filtering any records. Any suggestions? (and thanks again to everyone for your help. This forum has been fantastic.)

binary [\\path\file.qvw];

FCTLEADACTIVITYNEW:
NOCONCATENATE
LOAD *
RESIDENT FCTLEADACTIVITY WHERE (%gencampus_id=1 or %gencampus_id=2) and year(leaddate_timestamp)>=2014;
DROP TABLE FCTLEADACTIVITY;

DIMSTUDENTNEW:

NOCONCATENATE

LOAD *

RESIDENT DIMSTUDENT where exists (%genmksystudent_id, %genmksystudent_id);

DROP TABLE DIMSTUDENT;

DIMCAMPUSNEW:
NOCONCATENATE
LOAD %gencampus_id,%sycampus_id,campus
RESIDENT DIMCAMPUS

WHERE (%gencampus_id=1 or %gencampus_id=2);
DROP TABLE DIMCAMPUS;

Re: QVW load

isorinrusu — Wed, 14 Jan 2015 17:23:52 GMT

Adam, you don't have to (must not) put twice the %genmksystudent_id . It will sufffice with

RESIDENT DIMSTUDENT where exists (%genmksystudent_id);

The second field of the exists() function is an expression. In your case, putting the same field two times will only take the %genmksystudent_id with value %genmksystudent_id. Of course, the field and the value are 99.9% of the times different. Probably always.

Regards,

Sorin.

Re: QVW load

Wed, 14 Jan 2015 17:43:49 GMT

Thank you for the prompt response, Ionut. Unfortunately that didn't trim the records at all either. I still end up with my DIMSTUDENTNEW table having %genmksystudent_id records that aren't in the FCTLEADACTIVITYNEW table. Any other suggestions?