Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
Each table individually...
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
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;
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;
Thank you, Michael. That worked!
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;
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.
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?