Expression in Where Condition

[former_member402770]

Hi,

Could you please help with the issue while iam trying to validate the expression in the where condition, iam getting below two errors:

1) length @Select(Dim\Fiscal Period\Fiscal Month)=1

2) Case

when

length @Select(Dim\Fiscal Period\Fiscal Month)=1

then

'00'||(@Select(Dim\Fiscal Period\Fiscal Month))||'.'||@Select(Dim\Fiscal Period\Fiscal Year)

else

'0'||(@Select(Dim\Fiscal Period\Fiscal Month))||'.'||@Select(Dim\Fiscal Period\Fiscal Year)

End

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

try with this.also check if Fiscal Month  object is parsing or not.

length(@Select(Dim\Fiscal Period\Fiscal Month))=1

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Tried Fiscal Month is parsing also tried yours expression:

length(@Select(Dim\Fiscal Period\Fiscal Month))=1, still have the Invalid Expression Unsupported signature

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

it's working fine for me.. may be try to close the IDT and reopen again the universe and see.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

If i try this its working:

  @Select(Dim\Fiscal Period\Fiscal Month)=1

when i try this

  length(@Select(Dim\Fiscal Period\Fiscal Month))

it throws Unsupported Length(Decimal) , not sure why struck for while.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

try to put the "table.object" instead of object you have created.

Like

length(table.column name)=1

Fiscal Month mapping of table .column.

Amit

[former_member402770 · Accepted solution]

Hi,

I opened dimension fiscal month below is the snapshot:

Values in dfx:

In Blx:

   On Preview BLX Fiscal Month i get:

On Preview Show Values i get like below:

Tried closing IDT and opening same error as like my first post

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

issue is your object data type is Number.

use this one.First convert to in the string format and then use length function.

length(convert(@catalog('XYZ CONNECTION')."XYZ"."DW_FISCAL_PERIOD_D"."FISCAL_MONTH",varchar))=1

Amit

[former_member402770 · Accepted solution]

Hi Amit,

It worked now, but when i build case it throws an error like in my first post:

Case

when

length(convert(@catalog('XYZ CONNECTION')."ORDRDW"."DW_FISCAL_PERIOD_D"."FISCAL_MONTH",varchar))=1

then

'00'||(convert(@catalog('XYZ CONNECTION')."ORDRDW"."DW_FISCAL_PERIOD_D"."FISCAL_MONTH",varchar))||'.'||(convert(@catalog('XYZ CONNECTION')."ORDRDW"."DW_FISCAL_PERIOD_D"."FISCAL_YEAR",varchar))

else

'0'||(convert(@catalog('XYZ CONNECTION')."ORDRDW"."DW_FISCAL_PERIOD_D"."FISCAL_MONTH",varchar))||'.'||(convert(@catalog('XYZ CONNECTION')."ORDRDW"."DW_FISCAL_PERIOD_D"."FISCAL_YEAR",varchar))

end

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

What is the data type of Fiscal Year Object.What is the expected result are you looking for this case statement.

using in the Prompt condition or somewhere else?

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Data Type of fiscal year obj is same like fiscal month only difference is Fiscal Year at end instead Fiscal Month.

Expected result: 003.2014 iam looking in dimension(data type is String) here specifically, Could you please help with the prompt condition for my expected result as well. You have outputs present in the above post for your reference

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

try with below steps.

1)Create Month Object with data type String.with below syntax I am checking the  length and adding 00 in the month part.

ifelse(length(convert(@catalog('DMFIXMAM')."PUBLIC"."IDMFIXMAM"."Month",Varchar))=1,

concat('00',convert(@catalog('DMFIXMAM')."PUBLIC"."IDMFIXMAM"."Month",Varchar)),

concat('0',convert(@catalog('DMFIXMAM')."PUBLIC"."IDMFIXMAM"."Month",Varchar)))

2) Create Year object with data type string. concatenating . and year.

concat('.',convert(@catalog('DMFIXMAM')."PUBLIC"."IDMFIXMAM"."Year",varchar))

3) Create Month/Year object based on the objects above created.

concat(@Select(IX Monthly\Month),@Select(IX Monthly\Year))

4) check the values.

5) right click and create filter.Select the filter type "Business" and create the filter.Drag Month/Year objects and select as a prompt.

6) Final filter with Month  and year.

Amit