19 Replies Latest reply: Feb 14, 2017 8:37 AM by Peter Rieper RSS

    Bulk Formatting Fields

    Andy Dietler

      I wrote some code for reformatting fields in bulk from an Excel file. It works as follows:

       

      1. Rename the fields to Name_temp that are going to be formatted (so that you can load star and build the new versions without overlapping their names)
      2. Build a string of all the formatting commands
      3. 1 reload of the table with * and then the formatting string
      4. Drop all the Name_temp fields

       

      Suggestions and feedback welcomed.

       

      Field NameFormatFormat Type
      DOBMM/DD/YYYYDate
      End Date YearYYDate
      Postal Code00000Num

       

      //Load Formats

      Format:

      LOAD

          "Field Name" AS "Format Field",

          Format,

          "Format Type"

      FROM [lib://Public Content Library/Format.xlsx]

      (ooxml, embedded labels, table is Format)

      ;

       

      //Load Fact Table

      Fact:

      LOAD *

      From Fact.qvd;

       

      //Load Formats

      Let ii = NoOfRows('DD_Format') - 1;

      For i=0 to $(ii)

        Let vFormatField = Peek('Format Field',$(i),'DD_Format');

        Let vFormat = Peek('Format',$(i),'DD_Format');

        Let vFormatType = Peek('Format Type', $(i),'DD_Format');

       

        Rename Field [$(vFormatField)] to [$(vFormatField)_temp];

       

        If i=0 then

          Let vFormatString = '$(vFormatType)([$(vFormatField)_temp],''$(vFormat)'') AS [$(vFormatField)]';

        else

          Let vFormatString = '$(vFormatString),' & chr(10) & '$(vFormatType)([$(vFormatField)_temp],''$(vFormat)'') AS [$(vFormatField)]';

        end if

      Next

       

      // Apply Formatting

      Rename Table FD to FD_temp;

       

      NoConcatenate

      FD:

      Load

      *,

      $(vFormatString)

      Resident FD_temp;

       

       

      For i=0 to $(ii)

        Let vFormatField = Peek('Format Field',$(i),'DD_Format');

       

        Drop Field [$(vFormatField)_temp];

      Next

       

      Drop Table FD_temp;

        • Re: Bulk Formatting Fields
          Peter Cammaert

          This may be a bit shorter. I used an INLINE table to show what happens. Easily replaced by a LOAD FROM an external source:

           

          FormatTable:

          LOAD concat(SingleField, ', ') AS RenameColumns;

          LOAD [Format Type] & '([' & [Field Name] & '], ' & chr(39) & Format & chr(39) &') AS [' & [Field Name] & ']' AS SingleField;

          LOAD * INLINE [

          Field Name, Format, Format Type

          DOB, MM/DD/YYYY, Date

          End Date Year, YY, Date

          Postal Code, 00000, Num

          ];

           

          LET vRenameColumns = peek('RenameColumns');

          LOAD $(vRenameColumns) FROM Fact.QVD;

           

          DROP Table FormatTable;

           

          Best,

           

          Peter

            • Re: Bulk Formatting Fields
              Andy Dietler

              I like it. Any way to make it work for the case where not every field in the Fact table is defined in the Format table? I just want to leave all of the other fields alone.

                • Re: Bulk Formatting Fields
                  Peter Cammaert

                  So that was the question that you didn't ask?

                   

                  Will you always be reading facts or other data from QVDs?

                    • Re: Bulk Formatting Fields
                      Andy Dietler

                      Yeah, it kind of slipped my mind that that is what I was doing. It will always be a single table, mostly fact tables, this is just part of a transformation process that outputs QVDs with the modifications made.

                        • Re: Bulk Formatting Fields
                          Peter Cammaert

                          QlikView script has a few very useful functions to read QVD meta-data. This one should do what you want: Also contains a few tricks to make the code more compact.

                           

                          Formats:

                          MAPPING

                          LOAD [Field Name], [Format Type] & '([' & [Field Name] & '], ' &

                               chr(39) & Format & chr(39) &') AS [' & [Field Name] &

                               ']' AS FormatRename

                          INLINE [

                          Field Name, Format, Format Type

                          DOB, MM/DD/YYYY, Date

                          End Date Year, YY, Date

                          Postal Code, 00000, Num

                          ];

                           

                          LET vSeparator = '';

                           

                          FOR i = 1 TO QvdNoOfFields('Facts.QVD')

                            LET vColumnName = QvdFieldName('Facts.QVD', i);

                            LET vRename=vRename & vSeparator &

                                        ApplyMap('Formats',vColumnName,'['&vColumnName&']');

                            LET vSeparator = ', ';

                          NEXT

                           

                          LOAD $(vRenameColumns) FROM Facts.QVD;

                           

                          // Don't forget to clean up leftover variables

                           

                          Don't believe QV Desktop help about 0-based field numbers.

                           

                          Best,

                           

                          Peter

                      • Re: Bulk Formatting Fields
                        Rob Wunderlich

                        I've considered blogging this several times but figured no one cared about it but me. Here's how I "reformat" fields I load from a QVD. Like "RENAME USING Map", you can include fields that are not in the QVD. I've used this to set all dates to a different locale for example.

                         

                        FormatTable:

                        LOAD

                          Date(0, 'MM/DD/YYYY') as DOB,

                          Date(0, 'YY') as [End Date Year],

                          Num(0, '00000') as PostalCode,

                          Date(0, 'YYYY-MMM') as OtherField

                        AutoGenerate 0;

                         

                        Facts:

                        LOAD * FROM Fact.qvd (qvd);

                         

                        DROP TABLE FormatTable;

                         

                        The formats are inherited from the FormatTable fields, There is no need to apply the formatting functions on the load of the Fact table. While I've coded the FormatTable as a set of simple statements above, I'm sure you can adapt the idea to use your external file.

                         

                        -Rob

                        http://masterssummit.com

                        http://qlikviewcookbook.com

                    • Re: Bulk Formatting Fields
                      Peter Rieper

                      Good afternoon,

                       

                      is there a way to apply this kind of formatting in combination with BINARY LOAD?

                      There you should not load anything before the load-statement.

                      Guess that needs to be done then in the inititial database (backend)?

                       

                      Regards

                      Peter