QVW load

[#1]

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.

[maxgro]

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;

[#3]

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?

[Anonymous]

Each table individually...

[engishfaque]

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

[#6]

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;

[Anonymous]

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;

[#8]

Thank you, Michael.  That worked!

[#9]

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;

[isorinrusu]

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.

[#11]

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?