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;
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
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:
And it doesn't break the optimized load. Nice!
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
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
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.
Version 2.0.9.0
It's just a field in the Fact table QVD
Nice approach !
I've test it with several format. My conclusion is :
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
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
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