topic Re: Qlik View Load SQL Select Question in QlikView

Qlik View Load SQL Select Question

Thu, 12 Nov 2015 09:00:48 GMT

Hi,

Look at below two script:

Load A, B；

SQL Select field1 as A, filed as B from Table；

The result is cannot find the filed A；

But another one is

Load *；

SQL Select field1 as A, filed as B from Table；

This works, I am a bit confused why？？？

Re: Qlik View Load SQL Select Question

oknotsen — Thu, 12 Nov 2015 09:17:24 GMT

Don't bother about the "why". Don't rename things in your SQL to begin with . Do them in your preceding load.

Re: Qlik View Load SQL Select Question

nikhilgarg — Thu, 12 Nov 2015 09:21:31 GMT

Hey,

Do like:

Load field1 as A, field 2 as B；

SQL Select field1 , field2 from Table；

Your following script works bcoz you ahve written Load * and it is loading field1 and field2 in the final table .

Load *；

SQL Select field1 as A, filed as B from Table；

Re: Qlik View Load SQL Select Question

Peter_Cammaert — Thu, 12 Nov 2015 09:25:53 GMT

The availability of the AS keyword in SQL depends on the RDBMS you are using. To avoid having to check for a particular DB type and adjust your SQL statement accordingly, do as Onno says: rename your fields in the Precending load like in:

LOAD field1 AS A, filed AS B;

SQL SELECT field1, field FROM Table;

Re: Qlik View Load SQL Select Question

marcus_sommer — Thu, 12 Nov 2015 11:59:30 GMT

The reason is the Preceding Load which isn't a two-step loading (load first table and from there load the second one) - it's more a kind of specialized pipe-loading in one step.

edit: for a solution look to the answers above.

- Marcus

Re: Qlik View Load SQL Select Question

flipside — Thu, 12 Nov 2015 12:25:38 GMT

I can't speak for the others but in my experience both statements should work. You CAN use AS in the SQL portion, I do all the time and don't have problems. After the Load * version do you definitely get Field A in your data model?

flipside

Re: Qlik View Load SQL Select Question

Thu, 12 Nov 2015 13:31:57 GMT

yes, the sql after load * works, A is available.

Re: Qlik View Load SQL Select Question

Thu, 12 Nov 2015 13:37:37 GMT

Hmm, I know, but in my mind, it should be work like this, first load the SQL part, and after that Load the key word, Load part. In the blog, Preceding Load, it says, The fact that the SELECT statement is evaluated before the Load.

So I am still very very puzzled.

Re: Qlik View Load SQL Select Question

flipside — Thu, 12 Nov 2015 14:19:31 GMT

Are these actual scripts or re-written examples to demonstrate the issue? It may help to post the actual script if you can.

Re: Qlik View Load SQL Select Question

marcus_sommer — Thu, 12 Nov 2015 15:18:39 GMT

I think the reason is quite similar to the topic from these posting: Re: SQL alias for tables in QlikView. It will be a question of the order how qlikview performed the various handles which belonged to a preceeding load and it seems that at this time the field A didn't yet exists.

I think in this sense it's best practice not to make any transformations within the sql-part and perform all transformations within the load-parts, not only because qlikview-functions couldn't be executed from the database but also to don't mixup transformations from a maintainability point of view.

- Marcus

Re: Qlik View Load SQL Select Question

maxgro — Thu, 12 Nov 2015 19:12:12 GMT

is "A" or "a" available?

which database are you using? I tried on an Oracle dbms with ODBC and both your script works