2 Replies Latest reply: Jun 10, 2014 1:47 AM by Tricia Chan RSS

    concatenating fields issue - one value not showing

      I've got 2 fields: Project_ID and Seq_No

      Both always have values and would never be a null

      Simplified version:

      LOAD 

             SEQ_NO

           , PROJECT_ID

           , SEQ_NO & '|' & PROJECT_ID AS [%My_Key]

       

      It loads without errors, however the %My_Key values are not right

       

       

       

      I see this:

      SEQ_NO     PROJECT_ID     %My_Key

      100024          W200                 |W200     <-- I am expecting "100024|W200".  All the SEQ_NO have gone missing

       

      I've tried TEXT(SEQ_NO) & '|' & TEXT(PROJECT_ID) AS [%My_Key]

       

      I've checked spellings, loading with just 1 field - when there is only Seq_No by itself, it works.  It doesn't like the "|" nor the Project_Id being tacked on.  What's its problem?

        • Re: concatenating fields issue - one value not showing
          Tresesco B

          Try using text(), like:

          Text(SEQ_NO) & '|' & PROJECT_ID AS [%My_Key]

          • Re: concatenating fields issue - one value not showing

            Thanks Tresesco, I've tried TEXT(SEQ_NO) but it was a different issue after all - I worked out what was eating it.  It's soooo obscure .

             

            The problem was I used a "group by" and forgot to put the [%My_Key] in the "group by".  It never complained that anything was wrong though... so Qlikers, beware!!  If you get an issue like this, check your "GROUP BY".

             

            Here is the code:

            LOAD 

                   SEQ_NO

                 , PROJECT_ID

                 , SEQ_NO & '|' & PROJECT_ID AS [%My_Key]

                 , SUM(AMOUNT)

            FROM MyQVD.QVD

            (qvd)

            GROUP BY

                   SEQ_NO

                 , PROJECT_ID

                 , SEQ_NO & '|' & PROJECT_ID  <---- I forgot this

             

            This was annoying -- hopefully, QlikView will put a warning in the next SR or show a syntax error.  Because in SQL, this wouldn't run.