6 Replies Latest reply: Mar 31, 2017 12:01 AM by Rahul Pawar RSS

    Invalid column name

    Richard Judkins

      Hi all

       

      Hopefully this is relatively simple.

       

      I'm using Qlik Sense Server.

       

      I have an existing (quite complex) app that wasn't created by me.

       

      The bulk of data it pulls from a SQL OLAP database (please excuse the terminology if I 'm way off base!).

      The data is sales data, which includes Reference Numbers.

       

      Anyway, I'm just wanting to include a table that I'm wanting to attach - this table hold a small number of Reference Numbers that I want to exclude from the reporting. It's called Qlik Exclusions.

       

      So - REF_NUMBER = the main sales reference numbers.

      And SALE_REF_NUMBER = the reference numbers to be excluded.

       

      This is the script I use to load the reference numbers to be excluded (to be fair, it's the default script loaded when I attach the file)

       

      LOAD

          SALE_REF_NUMBER

      FROM [lib://AttachedFiles/Qlik Exclusions.xlsx]

      (ooxml, embedded labels, table is [Excluded Sales]);

       

      And the expression to exclude the sales - WHERE REF_NUMBER <> SALE_REF_NUMBER.

       

      I try to Load Data - and it errors, saying

       

      SQL##f - SqlState: S0022, ErrorCode: 207, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'SALE_REF_NUMBER'.

       

      Is it because I'm trying to combine, essentially, data from a SQL server table and data from an attached table?

        • Re: Invalid column name
          Muñoz Héctor

          Hi Richard,

          I think your problem could be soved using Exists() and Not() funtions:QlikView: Against Intuition | Lucian Cotea

          Regards,

          H

          • Re: Invalid column name
            Rahul Pawar

            Hello Richard,

             

            Trust that you are doing well!

             

            When you add predicate/where condition in your script then one of the field should be from base table and another field can be a part of other table joined to base table or hard-coded value. In this example you are trying to add SALE_REF_NUMBER which is from Qlik Exclusions.xlsx file. Instead of this use below where condition:

             

            Where Not Exists (REF_NUMBER, SALE_REF_NUMBER)
            

             

            Hope this will be helpful.

             

            P.S.: First load the Qlik Exclusions.xlsx file and then use the given where clause.

             

            Regards!

            Rahul

              • Re: Invalid column name
                Richard Judkins

                Thanks Rahul

                 

                Now getting

                Capture.JPG

                cheers!

                  • Re: Invalid column name
                    Devarasu R

                    Hi,

                     

                    Just make sure that "REF_NUMBER" exist in your source. can you share your app / full script, thanks

                    refer below,

                    Where Exists


                    Data:

                    LOAD

                        SALE_REF_NUMBER

                    FROM [lib://AttachedFiles/Qlik Exclusions.xlsx]

                    (ooxml, embedded labels, table is [Excluded Sales])

                    Where Not Exists (REF_NUMBER, SALE_REF_NUMBER);

                      • Re: Invalid column name
                        Richard Judkins

                        To be honest that's probably not possible - there's nearly a thousand lines of script already existing. And I barely understand most of it (I didn't write it, and I'm pretty new at Qlik and SQL).

                        There's at least a dozen other tables that are either joined or selected from as well.

                         

                        Thank you for your help though - I'll try a few other things.

                      • Re: Invalid column name
                        Rahul Pawar

                        Hello Richard,

                         

                        Thank you for your response. Please do the following steps to fix this issue.

                         

                        1. First load the table from Qlik Exclusions.xlsx file

                        2. Later load the table using SQL script (without applying the where clause)

                        3. Then do the resident load of table generated from step 2 and apply the where clause (Make sure to place NoConcatenate statement between original table and resident load; This will avoid auto Concatenation of tables)

                        4. Post that drop the the table generated by SQL script and use resident load table


                        Hope this will be helpful.


                        P.S.: You can assign a temporary name to table from SQL script and desired name to resident load table to avoid further ambiguities.


                        Regards!

                        Rahul