Calculated Dimension

[Anonymous]

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

[#2]

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

[Anonymous]

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

Thanks

[#4]

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,

[Anonymous]

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

[#6]

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.

[kaushiknsolanki]

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

[Anonymous]

Thanks Kaushik.

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

[Anonymous]

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??

[kaushiknsolanki]

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