Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wrote some code for reformatting fields in bulk from an Excel file. It works as follows:
Suggestions and feedback welcomed.
Field Name | Format | Format Type |
---|---|---|
DOB | MM/DD/YYYY | Date |
End Date Year | YY | Date |
Postal Code | 00000 | Num |
//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;
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
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
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.
So that was the question that you didn't ask?
Will you always be reading facts or other data from QVDs?
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.
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
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
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.
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.
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