How to use functions in IDT

[Former Member]

Hi,

I am trying to create dimensions in the business layer however I am having issue to create some due to the functions since I am a beginner.

I am trying to create the following fields:

1. Material Number - I want  to pull all the data from fact table in the dfx except for null values

2. Requirement type - I want to pull data from the fact table where the values in the field is 'AR'

3. Requirement qty - I want to pull data from the fact table where the values are greater than null values

4. Item is deleted - I want to pull all the data from fact table except for 'X'

5. Movement type - I want to pull only data with '261' from the fact table.

Material Number

Is coming from this field and in this field is where I would like to exclude 'Null' values

Requirement Type

Is coming from the field as below and here I where I only want to pull data with 'AR' value.

Requirement Qty

Is coming from the field below and I want to pull data > 'Null' value

Item is deleted

Is coming from the field below where I want to pull data except for 'X'.

Movement Type I will follow the logic as in Requirement Type.

Please assist me with the functions and also where I can learn move on how to use functions in Information Design Tool.

I know how to use 'CASE WHEN...THEN' which works like the "If...ElseIf..." function.

Thank you in advance and looking forward to learn better,

Thank you in advance for all the help.

[amitrathi239 · Accepted solution]

Hi,

Use this.

Material Number:Put the below condition in where clause of the object in business layer.

@Select(IX Monthly\File Name) IS NOT NULL

Requirement type:condition in where clause.

@Select(class name\File Name) ='AR'

Item is deleted:condition in where clause.

@Select(class name\File Name) <>'X'

Amit

[Former Member · Accepted solution]

Hi Amit,

Thank you so much. I will try this out however just a quick question.

For the "where" clause the file name will it be the one from the fact table or the dimension file name itself.

In another work would I be creating 2 dimensions.

1. Material which reads from "@catalog...."

2. Material which reads from "@catalog...." with a where clause that is set to be not null from "@Select(folder\Material)

Thank you again for helping out

[amitrathi239 · Accepted solution]

Hi

you need to put the condition in  the object where you want to exclude the null  values.

i don't know the requirement other option is to put the condition at webi query level to exclude  some values or exculde null values.

Amit