Former Member

### Formula for Extended merge Dim

Hi,

I am struggling with this for last one week. can anyone help me.

Attached the screen shot. I have query from 2 DP. one from table(Material)and another from Excel(ITEM)

I am doing an extended merge, so i can get matching as well as unmatching record

The formula i am trying =If IsNull([LWVAL])<>0 Then "A" ElseIf IsNull([V_LastWeek_Data]<>0) Then "B" ElseIf IsNull([LWVAL])<>0 And IsNull([V_LastWeek_Data]<>0) Then "C" Else "NotDefined"

1) 111 contains in both table (Material& Excel)

2)121 only contains in Excel

3)123 only contains in Materialcapture1.jpg

capture1.jpg (21.3 kB)
10|10000 characters needed characters exceeded

Oct 30, 2017 at 05:10 PM

hi Sudarsan,

try enabling format table-> "show rows with empty dimension values"

Regards

Sateesh B

10|10000 characters needed characters exceeded
• first thing isnull() is a boolean always returns 1 or 0 .so ,we can use =0 or =1 for comparisons .Do not use <> .

I would suggest to post some of both original and Excel records here and expected output ,so community can better help.

Regards

Sateesh

• Oct 19, 2017 at 01:56 PM

create detail variable lwaval upon the merged dimension (var1)

create detail variable lastweekdate upon the meged dimension (var2)

create variable result

=If IsNull([var1])=1 And IsNull([var 2])=1 Then "both NULL" ElseIf IsNull([var 1])=0 And IsNull([var2])=1 Then "only var 2 null" ElseIf IsNull([var 2])=0 And IsNull([var1])=1 Then "only var 1 null" Else "not null"

10|10000 characters needed characters exceeded
• Former Member

Hi Koen,

lwaval is feed from excel (Last week data)& lastweekdata is from ,measure in the table. instead of creating formula using measure can we do it in dimension level

have 2 data providers Q1 contain Material and Q2 contains ISBN. I am merging both at report level.

(Material and ISBN is list of numbers and both are string)

eg

Material ISBN Formual

111 111 A

121 122 B

123 24 C

Now I want to write a formula based on that saying that

if (Material =ISBN Then “A” (Eg:111)) ElseIf (if the number only in Material then “B”(eg. 121,123) Else (IF the number only in ISBN then “C”(eg.122,124 )))

• Oct 19, 2017 at 11:10 AM

which object is from which table?

10|10000 characters needed characters exceeded
• Former Member

Hi Koen,

I am merging Material.Id=Item.id

LWAVAL from Item and V_LastWeek_Data from material

• Former Member
Oct 25, 2017 at 09:55 AM

Hi Koen,

Thanks for help that you have done previously . instead of measure value (LWAVAL from Item and V_LastWeek_Data from material) I want to do it in dimenison level

I have 2 data providers Q1 contain Material and Q2 contains ISBN. I am merging both at report level.

(Material and ISBN is list of numbers and both are string)

eg

Material ISBN Formual 111 111 A 121 122 B 123 124 C

Now I want to write a formula based on that saying that

if (Material =ISBN Then “A” (Eg:111)) ElseIf (if the number only in Material then “B”(eg. 121,123) Else (IF the number only in ISBN then “C”(eg.122,124 )))