Bulk Formatting Fields

[andydietler]

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

[Peter_Cammaert]

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

[andydietler]

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:

[johnw]

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

[rwunderlich]

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

[rwunderlich]

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

[Peter_Cammaert]

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.

[andydietler]

Version 2.0.9.0

It's just a field in the Fact table QVD

[jhamard]

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

[rwunderlich]

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

[prieper]

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