Skip to Content
avatar image
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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Oct 30, 2017 at 05:10 PM

    hi Sudarsan,

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

    Regards

    Sateesh B

    Add comment
    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"

    Add comment
    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?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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 )))

    Add comment
    10|10000 characters needed characters exceeded