topic Re: Populate a field with value from another field when value is null in QlikView

Populate a field with value from another field when value is null

Wed, 21 Nov 2012 11:46:55 GMT

Hi!

I know this is an easy one, but I cannot find a lean and fast way to obtain the following, if not by using a couple of JOIN and WHERE statements.

There has to be a faster way!

Header 1	Header 2	Header 3
A		A
B		B
C	M	M
D		D
E	Z	Z

I would like to have what you can see in column 3.

Basically all values of column 2, and, when cell is empty, value of column1 should be filled in!

thank you

Re: Populate a field with value from another field when value is null

Gysbert_Wassenaar — Wed, 21 Nov 2012 11:54:39 GMT

You can check for nulls with if(len(trim(MyFieldName))=0,..,...). If you have all the fields already in your table in becomes a simple if(len(trim(Field2))=0,Field3,Field2) to fill Field2 with the value of Field3 if there was no value in Field2.

Re: Populate a field with value from another field when value is null

Wed, 21 Nov 2012 11:57:34 GMT

Dear Gysbert,

thank you for your answer.

But I'm sorry, my question was not 100% clear.

The table I was showing is a SQL table and I would like to reach a solution in the script, so that I have as output Field3.

thank you!

Re: Populate a field with value from another field when value is null

its_anandrjs — Wed, 21 Nov 2012 12:14:15 GMT

Hi,

In a Load write script like

Temp:

Your sql load

Header1,Header2,Header3

From Source;

Data:

load *,

if(len(Header2) = 0, Header1,Header2) as Field3

Resident Temp;

DROP Table Temp;

HTH

Regards,

Anand

Re: Populate a field with value from another field when value is null

jagan — Wed, 21 Nov 2012 12:15:25 GMT

Hi,

Try this script

LOAD

If(Len(Header2) > 0, Header2, Header1) AS Header3;

LOAD * INLINE [

Header1,Header2

C, M

E, Z

];

Hope this helps you.

Regards,

Jagan.

Re: Populate a field with value from another field when value is null

its_anandrjs — Wed, 21 Nov 2012 12:18:37 GMT

Hi,

Try this example

YourSqlLoad:

LOAD * inline

[

Header1,Header2

C, M

E, Z

];

Data:

load *,

if(len(Header2) = 0, Header1,Header2) as Header3

Resident Temp;

DROP Table Temp;

Regards,

Anand

Re: Populate a field with value from another field when value is null

Wed, 21 Nov 2012 12:24:02 GMT

thank you for your answers.

I think yours, Jagan, is a bit inverted (First there should be the "LOAD INLINE" and then the if statement)

but in the end what really was missing to my script is the if statement, so you deserve a recognistion too!

I; m just wondering, is using "0" [zero] a proepr way? woudln't be better to use somethign like IsNull?

thank you!