topic Re: Empty everywhere in App Development

Empty everywhere

victor_greffet — Mon, 11 Dec 2017 12:04:56 GMT

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

Re: Empty everywhere

Chanty4u — Mon, 11 Dec 2017 12:06:36 GMT

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

Re: Empty everywhere

victor_greffet — Mon, 11 Dec 2017 13:25:59 GMT

Waou.

Thank you very much.

Re: Empty everywhere

Chanty4u — Mon, 11 Dec 2017 13:49:44 GMT

Nice its worked

Re: Empty everywhere

victor_greffet — Mon, 11 Dec 2017 15:04:32 GMT

Yes it works pretty well, end of heavy script .

The table is filled at the end of a LOAD

Re: Empty everywhere

Chanty4u — Tue, 12 Dec 2017 07:29:20 GMT

great have a nice day

Re: Empty everywhere

victor_greffet — Wed, 13 Dec 2017 08:03:20 GMT

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

Re: Empty everywhere

vineetadahiya12 — Wed, 13 Dec 2017 09:35:47 GMT

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

Re: Empty everywhere

victor_greffet — Wed, 13 Dec 2017 12:31:35 GMT

thanks Vineeta