Create a flag for missing values in data

[#1]

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

[tresB]

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;

[sujeetsingh]

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  ;

[jagan]

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.