10 Replies Latest reply: Feb 18, 2016 1:50 AM by mayilvahanan ramasamy RSS

    Original value display after Autonumber()

    Shyam Sreeramula

      Hi Experts,

       

      I have a key column which has a string values. I converted it to numeric key column using the Autonumber() function.

       

      However, when I try to display the original column values, I could not get it displayed.

       

      Please check the sample document attached.

       

      I was able to display the original values of the column only after RESIDENT Loading but not normal load from QVD.

       

      Isn't there a better of doing it?

       

      How to display the original values of the column that is used in Autonumber function?

       

      Any help here is highly appreicated.

       

      Regards,

      Shyam.

        • Re: Original value display after Autonumber()
          Stefan Wühl

          Not sure what you mean with 'when I try to display the original column values'.

           

          On your first script tab:

           

          LOAD * INLINE [
              O1, O4
              A123, NorthSydney
              E123, Townhall
              F123, Katumba
          ];

          T2:
          Load AutoNumber(O1,'O1') as C1, O1 as T2O1, O4 AS C4
          Resident OTD2;
          //Drop Table TD2;

          OTD3:
          LOAD * INLINE [
              O5, O4
              Northern place, NorthSydney
              City CBD, Townhall
              Rural Region, Katumba
          ];

          T3:
          Load AutoNumber(O4,'O4') as C4, O4 as T3O4, O5 AS C5
          Resident OTD3;
          //Drop Table TD3;

           

          Here, you created key C4 one time using AutoNumber, one time without.

           

          On your second tab:

          CADM_Activity_Master:

          LOAD  Activity_Master_ID,

                Autonumber(IF(Len(Trim(RMI_PK))=0,0,RMI_PK)) AS RMI_PK_ID,

          Autonumber(IF(Len(Trim(RVA_PK))=0,0,RVA_PK)) AS RVA_PK_ID,    

          Autonumber(IF(Len(Trim(RT_PK))=0,0,RT_PK)) AS RT_PK_ID,

          Autonumber(IF(Len(Trim(Date_Response))=0,0,Date_Response)) AS Date_Id,

            Date_Response AS Actual_Date_Response    

               Resident AM;

             

           

          You are using AutoNumber() without the optional second argument for an AutoID, so the numbers for all three ID fields will be using the same 'bucket' of sequential numbers to count up.

           

          Maybe these two findings might help you in fixing your issues.

            • Re: Original value display after Autonumber()
              Shyam Sreeramula

              Hi swuehl,

               

              Thank your response.

               

              Sorry, in hurry I forgot to remove the first tab code.

               

              Please check the QVW attached to this reply. I am loading a few columns from the QVD.

              However, I have used Autonumber function on Date_Response column to join to other table.

               

              Now, I want the same column original values since I would have to display in the dashboard.

               

              Hence, I have included the same column name below the Autonumber() function column and given a different name.

               

              But, I get the number not the original dates in the display.

               

              Also, please let me know if using the optional second argument is mandatory. I have used more than 10 fields from different tables as Autonumber() columns to join them.


              Will this be a problem going forward?

               

               

              Thanks in advance.

               

              --Shyam

                • Re: Original value display after Autonumber()
                  jagan mohan rao appala

                  Hi,

                   

                  If you want to retain the original values you have to use the additional column with alias like below

                   

                  LOAD

                  AutoNumber(Date_Response ) AS Date_Response ,

                  Date_Response  AS Date_Response_Orig,

                  '

                  '

                  '

                  FROM DataSource;


                  Now use Date_Response_Orig column to display the actual values.


                  Hope this helps you.


                  Regards,

                  jagan.

                  • Re: Original value display after Autonumber()
                    Stefan Wühl

                    Yes, I think you just need to reformat your date field using formatting function Date().

                     

                    LOAD ...

                            Date(Date_Response) AS Actual_Date_Response

                    ...

                     

                    You can add an additional format code to date function as needed.

                     

                    I believe this has nothing to do with the autonumber, or are you saying that the date comes in formatted from the QVD when you are removing the autonumber()'ed ID fields in the LOAD?

                     

                    To answer your other question: the second argument is not mandatory, you just won't get sequential numbering guaranteed per ID field.

                • Re: Original value display after Autonumber()
                  Shyam Sreeramula

                  Thanks guys.

                  The Date function on the field worked.

                  Thanks heaps.

                   

                  Regards,

                  Shyam.

                  • Re: Original value display after Autonumber()
                    Shyam Sreeramula

                    Guys,

                     

                    Just a quick question, What if I have non Date field?

                    What functions should I use for Character fields?

                     

                    thanks in advance.

                     

                    Regards,

                    Shyam.