3 Replies Latest reply: Apr 26, 2017 7:11 AM by Stefan Wühl RSS

    Code correction required

    Arshad Hussain

      Can anyone tell me what's wrong with this code as I am not getting the desired result from the below mentioned code. Here I would explain what I am trying to do: actually I have a data in the field INTERFACE_HEADER_ATTRIBUTE1 like WWAILRNOPRN012013. The 02 digits starting from  position 12 vary from 01 to 24. The following check is also related with these 02 digits. Now the main point is that when the value is aforesaid 02 digits is between 01 to 12 the result is - while for values 13 to 24 the result is like e.g. 2013-14 which is desirable. So, please guide me what I am missing in the below code.

      =if(num(mid(trim(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1),12,2))<=12,num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))&'-'&num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))+1,num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))-1&'-'&num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4)))

        • Re: Code correction required
          Stefan Wühl

          You can't add +1 to a concatenated string, hence you need to use parantheses to indicate that you want to add +1 to the extracted year number:

           

          LOAD *,

          if(num(mid(trim(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1),12,2))<=12,

          num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))&'-'&(num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))+1),

          num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))-1&'-'&num(right(RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1,4))) as Test;

          LOAD * INLINE [

          RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_ATTRIBUTE1

          WWAILRNOPRN012013

          WWAILRNOPRN132013

          ];

           

          P.S. I think this issue has nothing to do with the geographic maps in Qlik, so please chose an appropriate place when posting.