Invalid column name

[Anonymous]

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?

[hector_munoz]

Hi Richard,

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

Regards,

H

[rahulpawarb]

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

[Anonymous]

Thanks Rahul

Now getting

cheers!

[devarasu07]

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);

[Anonymous]

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.

[rahulpawarb]

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