Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
Not applicable

Create a flag for missing values in data

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

Tags (3)
3 Replies
MVP
MVP

Re: Create a flag for missing values in data

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
Honored Contributor III

Re: Create a flag for missing values in data

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  ;

MVP & Luminary
MVP & Luminary

Re: Create a flag for missing values in data

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.