Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andydietler
Partner - Creator
Partner - Creator

Bulk Formatting Fields

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;

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

19 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

andydietler
Partner - Creator
Partner - Creator
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

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

andydietler
Partner - Creator
Partner - Creator
Author

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

sunny_talwar

rwunderlich that looks like a superb idea, I am not sure if I will ever get to use this, but I am def. going to try this out. Thanks for sharing.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

I tihnk there is one important distinction. IMHO inheriting a default format won't reject any values because they don't match the expected type, while the explicit LOAD with formatting functions will replace invalid values with NULLs. That may be useful if you want to combine load-cleanup-and-format in one load.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Peter,

"while the explicit LOAD with formatting functions will replace invalid values with NULLs."

I don't see this behavior.  In my experience formatting functions will return an unchanged value, not a null. Can you provide an example?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com