topic Re: Calculated Dimension in QlikView

Calculated Dimension

Anonymous — Wed, 08 May 2013 12:19:27 GMT

Hi all,

My client requested to create a calulated dimension using the below query

select * from db.TableName t

where (upper(XXX) = upper('value1') or

upper(XXX) = upper('value2'))

and YYY<> 'value1'

and ZZZ= 'value1'

Whether it is possible to create ??

Please help me.

Thanks

Re: Calculated Dimension

Wed, 08 May 2013 12:48:35 GMT

Yeah you can do this with a simple combination of if, or & and in your load script.

Re: Calculated Dimension

Anonymous — Wed, 08 May 2013 12:53:46 GMT

Could you please convert my above query into Qlikview script??

Thanks

Re: Calculated Dimension

Wed, 08 May 2013 13:08:39 GMT

I assume you're on a SQL database.

First connect to database, you'll get some script like:

ODBC CONNECT TO XXX (XUserId is Admin, XPassword is Admin);

SQL SELECT

NEW_TABLE_NAME:

IF(Upper(XXX)=Upper('value1') or (upper(XXX) = upper(value1) and upper(value 2) and YYY<> 'value1' ), Fieldname, null())

FROM DBS.dbo."YOUR_TABLE"

You'll have to create the IF statement for all fields in the table.

Let me know if this helps,

Re: Calculated Dimension

Anonymous — Wed, 08 May 2013 13:14:31 GMT

Actually in my query I have '*' (select * ...). So we need a particular field to do this right??

Re: Calculated Dimension

Wed, 08 May 2013 13:19:21 GMT

Once you're connected you get the option to select * or individual fields.

I don't know of any way (or need) to manipulate all fields in Qlikview. If there're many fields in the table you can just copy the fieldnames to Excel and create the proper statements.

Re: Calculated Dimension

kaushiknsolanki — Wed, 08 May 2013 13:23:07 GMT

Hi,

You can write the script such a way that it will fetch the data as you want. Something like shown below.

Set vVariable1 = ABC;

Set vVariable2 = PQR;

Sql Select * from table_name where (XXX = $(vVariable1) or XXX = $(vVariable2)) and

and YYY<> 'value1'

and ZZZ= 'value1'

Regards,

Kaushik Solanki

Re: Calculated Dimension

Anonymous — Wed, 08 May 2013 13:28:04 GMT

Thanks Kaushik.

But this query is only to create dimension. I think the above query will give me a table.

Re: Calculated Dimension

Anonymous — Wed, 08 May 2013 13:29:45 GMT

Actually this is the requirement my client gave to create a calculated dimension. So I would like to know whether we can create dimension using above query (using *) or we need a specific field to achieve this??

Re: Calculated Dimension

kaushiknsolanki — Wed, 08 May 2013 13:31:08 GMT

Hi,

Yes this will give you a table, but then you can use any of the field from this table in your dimension.

It will be good if you share the datamodel of your application to understand it in better way.

Regards,

Kaushik Solanki

Re: Calculated Dimension

Anonymous — Wed, 08 May 2013 13:37:36 GMT

Actually I just started developing the application. So datamodel to show as of now.

In the requirement doc I have a table and in that I need to create a calculated dimension using the query I have shared.

So I am confused whether we could create a dimension using the query as it is mentioned like "Select *"

Re: Calculated Dimension

kaushiknsolanki — Wed, 08 May 2013 13:40:56 GMT

Hi,

Understand one point in QlikView, first you write the script like Sql Statements and all and get the data in QlikView in table format. And once you have the data you can create charts by defining the dimensions and expressions

Hope this is clear.

Regards,

Kaushik Solanki

Re: Calculated Dimension

Anonymous — Thu, 09 May 2013 12:15:41 GMT

Thanks Kaushik & mrdaan