Empty everywhere

[victor_greffet]

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

[Chanty4u · Accepted solution]

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

[victor_greffet]

Waou.

Thank you very much.

[Chanty4u]

Nice its worked

[victor_greffet]

Yes it works pretty well, end of heavy script .

The table is filled at the end of a LOAD

[Chanty4u]

great have a nice day

[victor_greffet]

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

[vineetadahiya12]

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

[victor_greffet]

thanks Vineeta