topic Re: Create a flag for missing values in data in QlikView

Create a flag for missing values in data

Wed, 05 Mar 2014 09:01:45 GMT

Hi,

I have data which may have missing values. In the load script, I want to create a flag based on whether the value is populated or not.

I am using the ISNULL command - though the flag gets created, every record that has a missing value shows up twice in the data with the flag and with a - value.

I want just one row for each record with a single flag - any help appreciated.

qvw file appended for reference.

Regards,

Shweta

Re: Create a flag for missing values in data

tresB — Wed, 05 Mar 2014 09:12:39 GMT

Improper JOIN is the issue. Try like:

Data:

LOAD Name,

Value

FROM Data.xlsx (ooxml, embedded labels, table is Data);

//JOIN (Data)

Tab2:

LOAD *,

if(isnull(Value), 'No','Yes') as Value_Available

Resident Data;

Drop table Data;

Re: Create a flag for missing values in data

sujeetsingh — Wed, 05 Mar 2014 09:14:17 GMT

Try this

Directory;

Data:

LOAD Name,

Value

FROM

Data.xlsx

(ooxml, embedded labels, table is Data);

Left JOIN (Data)

LOAD

if(isnull(Value)=0, 'Yes','No') as Value_Available

Resident Data ;

Re: Create a flag for missing values in data

jagan — Wed, 05 Mar 2014 09:21:00 GMT

Hi,

Try this

Data:

LOAD Name,

Value,

if(isnull(Value)=-1, 'No','Yes') AS Value_Available

FROM

Data.xlsx

(ooxml, embedded labels, table is Data);

Regards,

Jagan.