topic Bulk Formatting Fields in QlikView

Bulk Formatting Fields

andydietler — Fri, 01 Jul 2016 19:38:12 GMT

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

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)
Build a string of all the formatting commands
1 reload of the table with * and then the formatting string
Drop all the Name_temp fields

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

// 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];

Drop Table FD_temp;

Re: Bulk Formatting Fields

Peter_Cammaert — Fri, 01 Jul 2016 20:10:13 GMT

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

andydietler — Fri, 01 Jul 2016 20:17:27 GMT

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 — Fri, 01 Jul 2016 20:25:40 GMT

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

andydietler — Fri, 01 Jul 2016 20:28:49 GMT

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 — Fri, 01 Jul 2016 21:13:39 GMT

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 = ', ';

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

rwunderlich — Sat, 02 Jul 2016 00:07:59 GMT

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

sunny_talwar — Sat, 02 Jul 2016 00:17:22 GMT

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.

Re: Bulk Formatting Fields

Peter_Cammaert — Sat, 02 Jul 2016 09:40:11 GMT

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.

Re: Bulk Formatting Fields

rwunderlich — Mon, 04 Jul 2016 14:53:10 GMT

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

Re: Bulk Formatting Fields

Peter_Cammaert — Mon, 04 Jul 2016 16:14:46 GMT

You're right Rob, that was very bad wording by me.

OTOH, I was already thinking about an even more flexible setup where a mapping table is filled with whatever expression you want (including interpretation functions or a bunch of IF() calls) and one or more markers to be replaced by the field name.

However, I can't find a reason why you would want to use a central field repository while a few customized load statements in include files (1 per table) would do the trick just fine. Costs & Benefits...

Doh. Nice idea, no application.

Peter

Re: Bulk Formatting Fields

andydietler — Thu, 07 Jul 2016 21:21:43 GMT

Hmm, that's not working for me in Qlik Sense.

I've got just your code snippet:

FormatTable:

LOAD

Date(0, 'MM/DD/YYYY') as [Birth Date]

AutoGenerate 0;

Facts:

LOAD

FROM [lib://Library/Fact.qvd]

(qvd);

DROP TABLE FormatTable;

exit script;

And I still get the original format:

Re: Bulk Formatting Fields

johnw — Thu, 07 Jul 2016 23:51:02 GMT

And it doesn't break the optimized load. Nice!

Re: Bulk Formatting Fields

rwunderlich — Fri, 08 Jul 2016 00:25:50 GMT

Hey Peter, I just noticed today that you are right about at least one formatting function -- num() -- returns null if the input is not a number.

-Rob

Re: Bulk Formatting Fields

rwunderlich — Fri, 08 Jul 2016 00:34:35 GMT

Andy, It works ok for me in Sense.

1. What version are you using?

2. Is [Birth Date] as master item or just a field?

-Rob

Re: Bulk Formatting Fields

Peter_Cammaert — Fri, 08 Jul 2016 00:38:45 GMT

Apparently so does

=Date('ABC') // Same in script

The difference may be that Date() accepts quite a lot of different formats (strings and numbers).and may give the impression to not care too much.

Re: Bulk Formatting Fields

andydietler — Fri, 08 Jul 2016 00:41:20 GMT

Version 2.0.9.0

It's just a field in the Fact table QVD

Re: Bulk Formatting Fields

jhamard — Thu, 08 Dec 2016 10:41:43 GMT

Nice approach !

I've test it with several format. My conclusion is :

It works well with Num(), Text()
But not with Timestamp(), Date(), Money(), Time(), Interval()

I'm using QV 11.2 SR11

Any idea/solution of the reason while it don't work with the date & time format ?

My script :

myTable:

LOAD

Text(0) as TXT

,Num(0) as NUM

,Money(0) as EUR

,Timestamp(42376.45) as TS

,Date(42376) as DATE

,Interval(0.375) as INTERVAL

,Time(0.375) as TIME

AutoGenerate 1;

LOAD * INLINE [

TIME, INTERVAL, EUR, TS, DATE, TXT, NUM

0.375, 0.375, 100, 42376.45, 42376, 5000, 5000

0.375, 0.375, 200, 42377.65, 42377, 52e40, 52e40

];

The result :

The yellow row is the AutoGenerated one.

Regards

Re: Bulk Formatting Fields

rwunderlich — Sun, 11 Dec 2016 04:45:21 GMT

Julien,

As you've noted, this technique will not automatically interpret numbers as Dates or Times when loading from Inline or Text file. You must include the Date() function in the Inline load. It will however, apply the default from the autogenerated "Default Format" load. For example:

SET DateFormat='M/D/YYYY';

FormatTable:

LOAD Date(0, 'YYYY-MM-DD') as OrderDate

AutoGenerate 0;

NoConcatenate

LOAD Date(OrderDate) as OrderDate INLINE [

OrderDate

43267

];

"OrderDate" will be correctly read as a Date but will be formatted YYYY-MM-DD, not the DateFormat='M/D/YYYY'.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Bulk Formatting Fields

prieper — Tue, 14 Feb 2017 13:37:50 GMT

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