topic Re: Replace with null in QlikView

Replace with null

stephenedberkg — Wed, 25 Nov 2020 16:16:04 GMT

if there no values in database i want to replace it by 0

Replace(ID,'','0') as ID

i am using this formula in script but i did't getting the answer.

Re: Replace with null

sunny_talwar — Fri, 04 Mar 2016 10:43:27 GMT

Try this:

If(Len(Trim(ID)) = 0, 0, ID) as ID

Re: Replace with null

Joaquin_Lazaro — Fri, 04 Mar 2016 10:46:08 GMT

Hi:

Try this Replace(ID,null(), 0) as ID

Joaquín

Re: Replace with null

Anonymous — Fri, 04 Mar 2016 10:47:46 GMT

check this as well, might be helpful for you?

How to replace Null() by a string value?

https://community.qlik.com/ideas/1905

Replace NULL (non-value) with numerical 0

NULL handling in QlikView

Re: Replace with null

avinashelite — Fri, 04 Mar 2016 10:49:54 GMT

you can try like this too

If(ID = '' or ID=null() or isnull(ID), 0, ID) as ID

Re: Replace with null

maxgro — Fri, 04 Mar 2016 10:52:09 GMT

it depends on the meaning of "no values"

alt(ID, 0) as ID

if(isnull(ID), 0, ID) as ID

len(trim(..........

as already suggested

Re: Replace with null

stephenedberkg — Fri, 04 Mar 2016 10:52:15 GMT

Dear sunindia

Tbl 1

ID Sales

1 100

2 200

3 300

4 400

Tbl 2

ID Lockperiod

1 1

2 2

3 4

see ID 4 doesn't have lock period so in this case i want 0 as lock period for ID 4

Re: Replace with null

sunny_talwar — Fri, 04 Mar 2016 10:54:10 GMT

Are you joining the two tables to add 0? How would table 2 know that there is a 4 that exists?

Re: Replace with null

avinashelite — Fri, 04 Mar 2016 10:56:26 GMT

Try like this :

Tbl 1

ID Sales

1 100

2 200

3 300

4 400

Tbl 2

ID Lockperiod

1 1

2 2

3 4

Temp:

LOAD ID as Key

resident

Tbl 2;

concatenate(Tbl 2)

LOAD ID ,

0 as Lockperiod,

resident

Tbl 1

where not exist(Key ,ID);

Re: Replace with null

stephenedberkg — Fri, 04 Mar 2016 10:57:02 GMT

If the ID is not matching with table 2 then for this ID i want to display lock period as 0

Re: Replace with null

sunny_talwar — Fri, 04 Mar 2016 10:58:08 GMT

May be you can do something like this:

Table1:

LOAD ID as ID1,

Sales

FROM Source1;

Table2:

LOAD ID,

LockPeriod

FROM Source2;

Concatenate (Table2)

LOAD ID1 as ID,

0 as LockPeriod

Resident Table1

Where not Exists(ID, ID1);

FinalTable:

LOAD ID1 as ID

Sales

Resident Table1;

DROP Table Table1;