topic Re: SQL alias for tables in QlikView in QlikView

SQL alias for tables in QlikView

Thu, 22 Oct 2015 05:38:04 GMT

Hi guys,

I am trying to dig into SQL a little bit, and I have noticed, that when I try to name a table in QlikView, using only SQL, then it doesn't rename the chosen table. My code for this is:

Select *

From FilteredAccount as Partners;

FilteredAccount is the table I want to query, and I want to rename it to Partners. But after Reloading, both in the UI (by creating table box) and in the table viewer, I see FilteredAccount as table name. Has anyone encountered this before?

Thanks for the answers!

Re: SQL alias for tables in QlikView

vikasmahajan — Thu, 22 Oct 2015 05:47:18 GMT

Before this

Select *

From FilteredAccount as Partners;

Give following command

Yourname:

Select *

From FilteredAccount as Partners;

HTH

Vikas

Re: SQL alias for tables in QlikView

HirisH_V7 — Thu, 22 Oct 2015 05:56:10 GMT

Hi,

Assigning the local name For the Table will help to transform data .

Hope this helps.

Re: SQL alias for tables in QlikView

Thu, 22 Oct 2015 05:57:41 GMT

Thanks Vikas,

it works. But can I do the renaming only by modifying the SQL part? In theory (from materials found on the web) the line FROM FilteredAccount as Partners; should rename the table, but it doesn't.

Thanks,

Tibor

Re: SQL alias for tables in QlikView

vikasmahajan — Thu, 22 Oct 2015 06:01:37 GMT

Yourname:

Load

filed1 as < Column Name> ,

filed2 as < Column Name> ,

filed3 as < Column Name>

Select *

From FilteredAccount as Partners;

Like this presiding load you give names to columns.

HTH

Vikas

Re: SQL alias for tables in QlikView

Thu, 22 Oct 2015 06:05:07 GMT

Hi,

it's interesting, because renaming columns like you have mentioned works just fine...where I bump into a problem is renaming tables...

Tibor

Re: SQL alias for tables in QlikView

vikasmahajan — Thu, 22 Oct 2015 06:14:24 GMT

Why you want to rename table name ? Is there specific requirement ?

Vikas

Re: SQL alias for tables in QlikView

Thu, 22 Oct 2015 06:18:19 GMT

I am just experimenting with SQL, and it is really interesting, that many things can be done in QlikView with SQL, but this basic thing, like renaming table, doesn't.

Tibor

Re: SQL alias for tables in QlikView

jagan — Thu, 22 Oct 2015 06:21:54 GMT

Hi,

You can rename the SQL table by giving alias, but you need to manually rename the Qlikview TableName.

Try like below

QVTableName:

Select *

From FilteredAccount Partners;

Instead of default table name, it is suggestible to manually give the Qlikview table name, it is a good practice.

Hope this helps you.

Regards,

Jagan.

Re: SQL alias for tables in QlikView

marcus_sommer — Thu, 22 Oct 2015 11:43:23 GMT

I think the reason is that the sql-query runs inside a kind of "container" which isn't really effected from the sql-statement itself. This meant, at first qv creates a table which get without an explicit written table-name above the sql-statement the (last part) from the database-tablename and then this qv table will be filled with the responses from the sql-query.

QV let the complete sql-statement untouched - it's only transferred to the odbc/oledb-driver. This meant a classical sql-statement which gives tables names like:

...

ABC as a

LEFT JOIN

XYZ as b

ON a.ID = b.ID;

are working without problems.

- Marcus

Re: SQL alias for tables in QlikView

mjm — Thu, 22 Oct 2015 12:14:21 GMT

Hello Tibor,

you can rename the table from within the load script as per the example below:

CustomersTable:

LOAD CustomerID,

Address,

City,

Customer,

ContactName,

Country,

Fax,

Phone,

PostalCode,

"world.Name";

SQL SELECT *

FROM SalesDB.dbo.Customers;

As you can see here I have named the table CustomersTable. If I did not have this line (including : ) then the table name of Customers taken from the SQL table would be used.

I believe this answers your question. If this does, please mark this as helpful and correct.

Thanks,

Mark James

Qlik

Re: SQL alias for tables in QlikView

Thu, 22 Oct 2015 14:08:21 GMT

Hey Mark

I've did what you have said, and my code can be seen below

//Partners:

SQL Select

Partner.name

FROM FilteredAccount as Partner;

Here, my aim was, to rename FilteredAccount to Partner. I've also commented out the "qlikview name" with ':'. If I Reload the script, then if I want to create a table box, and searching tables on the "New Table Box" frame the "Show system tables", I don't see a table with the name Partner. I only see a table with the name FilteredAccount.

So -it seems like , for me- renaming tables this way do not work in QlikView.

Tibor

Re: SQL alias for tables in QlikView

marcus_sommer — Thu, 22 Oct 2015 14:46:49 GMT

Like above described this won't work - you need to name your tables explicitly within the qlikview-part not within the sql-part.

Further by many sql-tables might be a rename-statement per mapping helpful.

- Marcus

Re: SQL alias for tables in QlikView

Thu, 22 Oct 2015 14:49:48 GMT

Yeah, than the only way to rename is in the QlikView part......

Thanks guys for the comments and the help, I really appreciate it.

Best regards,

Tibor