Formula for Extended merge Dim

[Former Member]

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

[sateesh_kumar1 · Accepted solution]

hi Sudarsan,

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

Regards

Sateesh B

[Former Member · Accepted solution]

yea i did that as well

[sateesh_kumar1 · Accepted solution]

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

[Former Member]

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

[kohesco]

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"

[Former Member]

Hi Koen, Thanks for the formula. I am able to implement this, but next stage when i try to create a section on this, i am only able to see the "Both Null" OR I when i try to apply filter on it . i can only see the "Both Null"

cap1.jpg

[kohesco]

add material twice in the table and hide one and set section on the other, what happens than ?

[Former Member]

Oh Still the same 😞

cap2.jpg

[Former Member]

I have also tried to bring the formula twice in report block and create a section in one of them.

i have try to apply filter but only "Both Not Null" is appeairng

cap3.jpg

[kohesco]

Strange, in my example it works with a section as well, I didn't flag the extend merge dimension propertie, could you try it with unflagged property please ?

[Former Member]

If i removed extend merge dimension properties then only matching records from Material and ISBN table will come. but i want matching and unmatching. so i can write the condition based on that.

[Former Member]

Hi Koen,

lwaval & lastweekdata are measure value

[kohesco]

and are they specially defined ? running sum / average / other kind of database delegated?

[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 )))

[kohesco]

which object is from which table?

[Former Member]

Hi Koen,

I am merging Material.Id=Item.id

LWAVAL from Item and V_LastWeek_Data from material