Forum Discussion

DouweMeer's avatar
DouweMeer
Icon for Impactful Individual rankImpactful Individual
5 years ago

Expressions that yield variant data type cannot be used

I created a logic to combine a product description from multiple excel files. At one point it started giving me a data variant error so I was looking for numbers in my expression. I was unable to find the number so I tried something 'silly'. The result was unexpected:

 

Trying to improve, I tried the following, which 'worked' as in that the error was gone. 

 

Curious that this one is accepted in a text column

This is accepted

 

When placing the "1" on any of the other position, the error returns and sticks. But when you double on one of the VAR's, the issue is gone. 

 

 

Removing the single "1" and replacing it by a blank() does not return the error. Also, replacing the "1" by just 1, does solve the problem as well. 

 

 

I have no clue how to solve it. 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    DouweMeer , With data type text and blank(), it should not give this. Try to move a6 as first and try.

     

    I tried this and it worked

    Measure = VAR A1 = blank() 
VAR A2= blank() 
VAR A3 = blank() 
VAR A4 = 4
return switch(True() , 
A1<> BLANK(),A1 ,
A2<> BLANK(),A2 ,
A3<> BLANK(),A3 ,
A4<> BLANK(),A4 
)
  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi DouweMeer ,

    Based on your description, perhaps you can try to put the a6 variable in the last swtich() statement to get the result:

    Column =
VAR a1 =
    BLANK ()
VAR a2 =
    BLANK ()
VAR a3 =
    BLANK ()
VAR a4 =
    BLANK ()
VAR a5 =
    BLANK ()
VAR a6 = "1"
RETURN
    SWITCH (
        TRUE (),
        a5 <> BLANK (), a5,
        a2 <> BLANK (), a2,
        a4 <> BLANK (), a4,
        a1 <> BLANK (), a1,
        a3 <> BLANK (), a3,
        a6 <> BLANK (), a6,
        BLANK ()
    )

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • DouweMeer's avatar
      DouweMeer
      Icon for Impactful Individual rankImpactful Individual
      5 years ago

      v-yingjl 

      Strange that this removes the behavior. Although extremely unfavourable as this expression is meant to swtich between product tabels dependable on priority. In some fields some of the sources do not have a related data field and therefore a blank is entered in the VAR. If I would change the position for each field to make sure that the last VAR is always filled in, it becomes a mess to write, maintain, and check on it. 

      I'll report a ticket to make sure they fix this. It shouldn't be like this.