6 Replies Latest reply: Aug 16, 2012 1:09 PM by Jason Michaelides RSS

    Edit Qlikview Script

      Hi,

       

      New to Qlikview and just come across my first hurdle!  I am importing data from 2 sources, first source is from an ODBC source, second is from an Excel spreadsheet.  The data imports fine however I want to restrict my import by adding a where clause to restrict the import, this was possible in Access with an inner join but obviously with relationships this isn't possible.  Can I add a where clause in the edit script or do I import all of the data first and then add a condition somewhere in the expressions of the chart properties?

       

      Help would be appreciated.

       

      Thanks

        • Re: Edit Qlikview Script
          Jason Michaelides

          You can add WHERE clauses in the script.

           

          From ODBC:

           

          LOAD

               Field1

               ,Field2

               ,etc

          ;

          SQL SELECT blah blah blah

          FROM table WHERE blah blah blah;

           

          From Excel:

           

          LOAD

               Field1

               ,Field2

               ,etc

          FROM Excel...(ooxml stuff etc...)

          WHERE blah blah blah;

           

          Hope this helps,

           

          Jason

            • Re: Edit Qlikview Script

              Thanks Jason,

               

              Yes this makes sense and answers half my question as in the place to add the clause is in the script editor.  However my where clause is trying to add a condition that says where

               

              field 1, table 1 (excel)

              equals

              field 1, table 1 (odbc)

               

              In affect I am trying to join the two data sources on a common field to restrict the amount of data I bring back, hope that makes more sense?

                • Re: Edit Qlikview Script
                  Jason Michaelides

                  QlikView will automatically join on all common fields (Field1 in this case).  So, assuming you only have a single common field between the two data sources, and you only want to pull from the Excel source where data exists in the ODBC source, try this:

                   

                  Table1:

                  LOAD

                       *

                  ;

                  SQL SELECT * FROM Table...;

                   

                  Table2:

                  LOAD * FROM Excel....

                  WHERE Exists(Field1);

                   

                  Hope this helps,

                   

                  Jason

                    • Re: Edit Qlikview Script

                      Thanks Jason,

                       

                      This doesn't seem to do anyhting, never mind I will have to carry on trying to figure out why it won't work.  Very frustrating as it makes sense to me but I am still not getting a restricted data set when I run this.

                      • Re: Edit Qlikview Script

                        Full script if it helps, I am trying to only display the numbers fromthe Closed .xls spreadsheet that match the numbers in the larger odbc numbers in the ""Prod".dbo.reporting" db

                         

                        SET

                        ThousandSep=',';

                        SET DecimalSep='.';

                        SET MoneyThousandSep=',';

                        SET MoneyDecimalSep='.';

                        SET MoneyFormat='£#,##0.00;-£#,##0.00';

                        SET TimeFormat='hh:mm:ss';

                        SET DateFormat='DD/MM/YYYY';

                        SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                        SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

                        SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';



                        ODBC CONNECT TO [xyz];



                        //-------- Start Multiple Select Statements ------

                        SQL SELECT Sales, Invoice, Date

                        FROM "Prod".dbo.sales;

                        SQL SELECT Number, F as 'Sales'

                        FROM ""Prod".dbo.reporting;

                        //-------- End Multiple Select Statements ------

                        Closed:

                        LOAD Number
                        FROM

                        [..\..\Closed.xlsx]

                        (
                        ooxml, embedded labels, table is Sheet1)

                        Where Exists (Number);

                         

                          • Re: Edit Qlikview Script
                            Jason Michaelides

                            Try using a preceding load for your ODBC selects:

                             

                            //-------- Start Multiple Select Statements ------

                            Table1:

                            LOAD

                                Sales,

                                Invoice,

                                Date

                            ;

                            SQL SELECT Sales, Invoice, Date

                             

                            FROM "Prod".dbo.sales;

                             

                            Table2:

                            LOAD

                                Number,

                                Sales

                            ;

                            SQL SELECT Number, F as 'Sales'

                             

                            FROM ""Prod".dbo.reporting;

                             

                            //-------- End Multiple Select Statements ------

                             

                            Closed:

                             

                            LOAD Number

                            FROM

                             

                            [..\..\Closed.xlsx]

                             

                            (ooxml, embedded labels, table is Sheet1)

                             

                            Where Exists (Number);

                             

                            Try that to see if it works, however if the only field you want is Number from the Excel sheet and actually you just want to flag ODBC records that are closed then ApplyMap() will probably suit you better.

                             

                            Hope this helps,

                             

                            Jason