22 Replies Latest reply: Jan 14, 2015 2:05 PM by Adam Kovarik RSS

    QVW load

      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
          qlik777 view777

          try put write

           

          where condition under binary statment   like

           

          where sales>=10000;

          • Re: QVW load
            Gabriel Rocha

            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
              Felix Snijders

              Hi,

               

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

               

              hth

              F.

              • Re: QVW load
                Clever Anjos

                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
                  Massimo Grossi

                  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

                    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
                      ISHFAQUE AHMED

                      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

                        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;

                        TableStructure.png

                        • Re: QVW load

                          Thank you, Michael.  That worked!

                          • Re: QVW load

                            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
                                I. Sorin Rusu

                                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

                                    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?

                                  • Re: QVW load
                                    Michael Solomovich

                                    Adam,

                                     

                                    The exists() function looks for a field in all previously loaded data, not just the previous table.  In your case, it exists in the table DIMSTUDENT.  You can do something like this:

                                     

                                    FCTLEADACTIVITYNEW:
                                    NOCONCATENATE

                                    LOAD *, %genmksystudent_id as existingstudent
                                    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 (existingstudent, %genmksystudent_id);

                                    DROP TABLE DIMSTUDENT;

                                     

                                    DROP FIELD existingstudent;