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
hi Sudarsan,
try enabling format table-> "show rows with empty dimension values"
Regards
Sateesh B
yea i did that as well
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
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"
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"
add material twice in the table and hide one and set section on the other, what happens than ?
Oh Still the same ):
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
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 ?
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.
Hi Koen,
lwaval & lastweekdata are measure value
and are they specially defined ? running sum / average / other kind of database delegated?
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 )))
which object is from which table?
Hi Koen,
I am merging Material.Id=Item.id
LWAVAL from Item and V_LastWeek_Data from material
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 CNow 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 )))