Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I have a question, i'd like to know if there is an awesome statement or a piece of script wich could do :
- Put 'empty' for all blanks in my database, (calculate field or a loaded field or whatever...)
I don't want to use
if(len(trim([IField]))=0,'empty',[Field]) AS [Field] for each field, I'd like to have a general statement wich appear once in my script...
Do you have an idea ?
Thanks,
Victor
am not sure try this
NullAsValue *;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NullValue='<NULL>';
SET NullDisplay='Null';
ODBC CONNECT TO ...
am not sure try this
NullAsValue *;
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET NullValue='<NULL>';
SET NullDisplay='Null';
ODBC CONNECT TO ...
Waou.
Thank you very much.
Nice its worked
Yes it works pretty well, end of heavy script .
The table is filled at the end of a LOAD
great have a nice day
Still Working on it, I realize it works each Load from a file. when I do a left join of a correspondence table and miss some value in it, it put blank, not 'empty' in my final table for the joined column only.
Do you have an idea ?
thks
Hi Victor,
Try this if works.
Use statement Set NullInterpret= ''; // This will read all nulls
I am putting the sample code for your refernce, where the Null values as a result of Right Outer Join replaced to show Empty and make it selectable.
Set NullInterpret = '';
NullReplace:
Mapping LOAD * INLINE
[
Value , ReplaceWith
'' , 'Empty'
];
Map * using NullReplace;
////// YOUR LOGIC////////
table1:
LOAD * INLINE [
Region, EmpName, Date, ActivityCode
SA-I, C, 5/12/2017, ACT2
SA-I, C, 6/12/2017, ACT6
SA-I, C, 6/12/2017, ACT7
SA-I, C, 6/12/2017, ACT8
SA-II, A, 1/12/2017, ACT1
SA-II, A, 2/12/2017, ACT3
SA-II, A, 3/12/2017, ACT4
SA-II, B, 1/12/2017,
];
right join
LOAD * INLINE [
Region, EmpName1, Date1, ActivityCode1
SA-I, C, 15/12/2017, ACT2
SA-I, C, 16/12/2017, ACT8
SA-II, A, 11/12/2017, ACT1
SA-III, B, 11/12/2017,
];
/// Below logic is required to mark the null values generated as a result of Join to replace those with Empty
FinalTable:
Load *,1 Resident table1;
Drop Table table1;
Hope this helps!!
Thanks,
Vineeta
thanks Vineeta