topic how to replace null values with out using resident load in QlikView

how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 16:09:04 GMT

hi,

i have 2 tables

one is master table contains two fields (%BILLINGDOC,sysfillener )two columns,

Another one is vendor table which contains two fields(%SYSLIFNR ,NAME1) , after i joining to tables based on the key column i am get null values in in that place i want replace with 'other_vendor',

My Script is

LOAD

%BILLINGDOC,

%SYSLIFNR

FROM

(qvd);

left join

LOAD

%SYSLIFNR,

NAME1

FROM

(qvd);

My output will be like this:

Header 1	Header 2	Header 3
%SYSLIFNR	%BILLINGDOC,	NAME1
US	65416823276	-
us	34665890670	-
us	56874474987	-
us	74084698396	-
us	73749840865	-
us	95476879878	-
uk	45676909889	-
us	43569078988	-
us	47987289808	vwr
uk	53874792389	ios
gd	46984750070	-
us	52783479739	-
uk	38746749801	skk

i want replace null values with 'other_vendor',with out using resident load for master table and apply map also.

Can any one help me on that..

Thanks in advance.

regards

siva reddy

Re: how to replace null values with out using resident load

swuehl — Fri, 29 Jan 2016 18:21:31 GMT

i want replace null values with 'other_vendor',with out using resident load for master table and apply map also.

That's what I had in mind, using a MAPPING approach. Why do you don't want to consider this?

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 18:28:58 GMT

Maybe use NullAsValue

SET NullValue = 'other_vendor';

NullAsValue NAME1 ;

..... your script

NullAsNull NAME1 ;

Re: how to replace null values with out using resident load

HirisH_V7 — Fri, 29 Jan 2016 18:58:31 GMT

Hi,

For field,

If(isnull(Header 3),'Other_Vendor',Header 3) as Header 3

Hope this helps,

Hirish

Re: how to replace null values with out using resident load

swuehl — Fri, 29 Jan 2016 19:06:17 GMT

I still would favour ApplyMap():

Don't join - use Applymap instead

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 19:24:18 GMT

With apply map, performance is very low. That's why I avoid apply map. Is there any other way to replace null values?

Thanks

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 19:29:45 GMT

its working but i want to use without resident load of master table.

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 19:34:47 GMT

I find ApplyMap() high performance.

But when loading from a qvd it will only be a fast optimised load if there is no data manipulation except a single WHERE EXISTS.

So as an ApplyMap() manipulates data this a non optimised load could be the low performance you have seen.

Why not put the ApplyMap() in the qvd generator and only generate a single qvd ?

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 19:44:00 GMT

yes that's correct,

but my requirement is not 2 fields in master table i have 500+ columns, if i use apply map or resident load for the single filed i that will affect to entire application .

Re: how to replace null values with out using resident load

Anonymous — Fri, 29 Jan 2016 19:49:07 GMT

Hi Bill,

Thanks for your answer, but i didn't get desired result. if any other ways will be give me that will help a lot.

regards

siva

Re: how to replace null values with out using resident load

MarcoWedel — Tue, 02 Feb 2016 22:05:22 GMT

Hi,

if you are that concerned about your loading performance you could as well forget about the joining bit and use Hirish's solution in the front end:

table1:

LOAD * INLINE [

%SYSLIFNR, %BILLINGDOC

US, 65416823276

us, 34665890670

us, 56874474987

us, 74084698396

us, 73749840865

us, 95476879878

uk, 45676909889

us, 43569078988

us, 47987289808

uk, 53874792389

gd, 46984750070

us, 52783479739

uk, 38746749801

];

table2:

LOAD * INLINE [

%BILLINGDOC, NAME1

47987289808, vwr

53874792389, ios

38746749801, skk

];

hope this helps

regards

Marco