topic Generate one line SQL Script in QlikView

Generate one line SQL Script

Wed, 23 Feb 2011 17:48:13 GMT

Hi qvcommunity!

I am often in need to generate SQL Scripts for an ETL we use. I'd love to use qv as a SQL Script generator.

The idea is to use the sqltable function to read meta data and then using qv functions to change the name of e.g. columns.

My question is how could i flatten a table in order to output one string and add e.g. a CREATE TABLE Statement?

Any Ideas?

Thanks

Andi

Generate one line SQL Script

pover — Wed, 23 Feb 2011 22:04:17 GMT

Hello Andi,

Can you upload a sample of you metadata and the result you want so as to better understand your question?

You might also download and install the QV SAP Connector which contains a QV SAP ScriptBuillder that uses SAP metadata to contruct QlikView Load Script. It might give you some ideas for creating your own SQL Scriptbuilder.

Regards.

AW:Re: Generate one line SQL Script

Thu, 24 Feb 2011 09:51:40 GMT

Hey Karl!

Thanks for the hint. I will have a look at the SAP Connector.

In general the problem is as following.

I've got n tables with n rows and 2 columns namely ID, ColumnName.

The table looks like:

=======Orders =====

ID; Column

==================

1; OrderID

2; OrderItem

3; Order Amount

4; .........

=======Customers =====

ID; Column

==================

1; Customer_ID

2; CustomerName

3; Customer......

The output should be 1 Table with

=====SQL_Queries=====

Tablename; SQL_Query

====================

Orders; SELECT Order_Id, OrderItem, OrderAmount, ... FROM Orders

Customers; SELECT Customer_ID, CustomerName, Customer.... FROM Customers

So precisly my question really is how to pivot a column to a row.

Thanks for suggestions

Andi

AW:Re: Generate one line SQL Script

pover — Thu, 24 Feb 2011 20:20:30 GMT

Andi,

Here is an example of what I think you are looking for. I added a third column which is the table name. If you don't have that information in the table you should be able to generate it in the QV Load Script with one of the Table Functions you can find in QV Help.

Regards.