14 Replies Latest reply on Jan 26, 2012 9:38 PM by LarryR

    Type Mismatch 'varValues', conditional mapping

    tyson33
      Hi, I'm working on a conditional map where a Custom 3 is marked with [None] anytime an account begins with a specific 2 digits. When running iin the Web's script editor, he script 13 - type Mismatch: varValues'

      If Left(varValues(13), 2) = "75" Then Result = "None"
      Else
      Result = strField
      End If

      What can you see wrong with this, and also, am I making this conditional map correct?


      Thanks!

      Edited by: tyson33 on Jan 24, 2012 11:53 AM
        • 1. Re: Expected 'End', conditional mapping
          LarryR
          Try putting

          Result = "None"

          on its own line.


          If you mean to assign the Result to the source value, then I don't think Result = strField will work. You will need the varValues(x) that corresponds to the dimension you are trying to reference. See the varValues table in the Mapping Tables chapter of the FDM Admin Guide for dimension reference numbers.
          1 person found this helpful
          • 2. Re: Expected 'End', conditional mapping
            tyson33
            Hi Larry,

            Moving the rest of the code down (leaving Then on top line) worked. Thank you.

            I now have a new error is Type Mismatch: "varValues'.


            (I should have kept the thread the same, but changed the title.)
            • 3. Re: Expected 'End', conditional mapping
              LarryR
              Can you post the entire contents of your script?
              • 4. Re: Expected 'End', conditional mapping
                tyson33
                Thats it, very simple.

                If Left(varValues(13), 2) = "75" Then
                Result = "None"
                Else
                Result = strField
                End If

                End Function


                varValues(13) is source account.

                Now my plan was to do a * to #Script and then do a "like" mapping in the custom mapping. (i'll remove the "end function" line then.

                error again:
                13-Typme mismatch: 'varValues'
                • 5. Re: Expected 'End', conditional mapping
                  LarryR
                  Yes, this script will only work in the #SCRIPT section of your Like map. It will not work in an Import script as it is. Try this in your #SCRIPT:

                  If Left(varValues(13), 2) = "75" Then
                  Result = "None"
                  Else
                  Result = varValues(13)
                  End If
                  • 6. Re: Expected 'End', conditional mapping
                    tyson33
                    Larry,
                    with this Like mapping in the Custom dimension, will I have to remove explicit mapping? There is a large amount of maps that I still need to use. I think it may be explicitly mapping instead of using the #Script.

                    Thanks
                    • 7. Re: Expected 'End', conditional mapping
                      tyson33
                      Let me clarify,
                      I wanted a limited about of Accounts to make a None value for Custom 3. For the accounts that are not starting with 75, the Custom 3 mapping remains the same
                      The maps are so large that they are probably already explicitly mapped. Is the #script able to take precedence ?
                      • 8. Re: Expected 'End', conditional mapping
                        tyson33
                        To anyone interested in my solution:
                        The explicit mapping sat in Cust 3, and I could not override this with a Like/#SCRIPT mapping, So I made a separate dimension where all of my explicit mapping sits and then my #SCRIPT is the only thing in Custom 3.

                        My script then says Custom 3 is then [None] wherever the account requires it to be and it also references the dummy dimension for all else.

                        Is there a better way to do this? i.e. not having to make a separate dimension and possibly working it all out in the Import format?

                        Thanks
                        • 9. Re: Expected 'End', conditional mapping
                          LarryR
                          You could do this in an Import script, similar to your Like mapping #SCRIPT, but using fParseString instead of varValues(x).

                          As an example, suppose that your source is tab [Chr(9)] delimited, there are 10 fields in your source, and account is the source's third field:
                          Function C3(strField, strRecord)
                          
                          If Left(DW.Utilities.fParseString(strRecord,10,3,Chr(9)),2) = "75" Then
                               C3 = "ACC_75X"
                          Else
                               C3 = strField
                          End If
                          
                          End Function
                          This code is untested, so modifications may be necessary.

                          Assign the C3 script to your Import Format's Custom3 source field and add ACC_75X to your Custom3 Explicit mapping table with a target of None. ACC_75X is just a descriptive string that indicates to the user that the source Custom3 field was modified upon import based on the account field beginning with 75. You could use any string, but the string should be descriptive enough to clearly communicate this purpose. This still does not make the import modification 100% transparent to users, which is why I always try to keep all transformations in the mapping tables if at all possible. In your case, though, it sounds like this would require a lot of re-work and possibly more maintenance. There are problably a few alternatives to what I've detailed here, each with advantages and disadvantages. Searching past posts on this forum may provide some alternative ideas.
                          1 person found this helpful
                          • 10. Re: Expected 'End', conditional mapping
                            tyson33
                            Hi Larry, I appreciate the time you spent here to assist me.

                            Currently, I just have one Like script in Custom 2 and I put my Explicit mapping in Custom 5.

                            If (varValues(13)>"1000" AND varValues(13)<"3000") Then
                            Result = [None]
                            ElseIf varValues(13) = "3424"
                            Result = [None]
                            ' ect...

                            Else
                            Result = varValues(28)

                            I put an explicit mapping in UD5, however the varValues(28) (Target UD5) is not getting my Custom 3 to get populated with UD5's target data. When I did it the other way (explict mapping in Custom 2 and referencing varValues(22) from Custom 5, it worked just fine.

                            Any thoughts on why the explicit mapping in UD5 is not picking up with varValues(28) ?
                            HFM does not have a dimension 5, so it was used as a "dummy" custom.
                            • 11. Re: Expected 'End', conditional mapping
                              LarryR
                              Tyson, sorry, I can't see what's causing the issue, but I've never tried anything quite like your approach. Can anybody else see why this wouldn't work?
                              • 12. Re: Expected 'End', conditional mapping
                                tyson33
                                Larry, this project has tons of exceptions and ranges that look at account to put a [None] for c3. I'm following your prior reply about creating this in the import script. Will this have a big impact on performance? It seems so.
                                • 13. Re: Expected 'End', conditional mapping
                                  LarryR
                                  In an Import script, the performance impact of conditional multi-field calculations is usually minimal. If you had many cross-dimensional conditions in Between, In, And/Or Like map #SCRIPTs, then the performance impact would probably be significant. In a nutshell, Import scripts generally perform better than mapping #SCRIPTS, but mapping #SCRIPTS tend to be more transparent to the user.
                                  • 14. Re: Expected 'End', conditional mapping
                                    LarryR
                                    If you are settling on an Import script, and you have many similar sources that translate to the same result, then you may want to use a Case statment instead of If. Case tends to be more efficient than If, and can save a lot of typing.