select in field action in qlikview

[prasoon92]

I have requirement where some of the Jobs have status NULL. i need to filter out these but the select in field is not working on the null values. Anything else can be done here?

[Anonymous]

if you can identify this data during extraction or in data model you may define a parametr which you may select

if (isnull(Status), 'N/A' Status) as Status

[prasoon92]

I checked that but requirement is like i am getting data in face only for the planned jobs. suppose there are 1000 jobs out of which only 950 are going for plan then only those are coming in facts but for other 50 we don't have any starus flag in the model. so for these 50 the data is NULL. but we neee to show these 50 also in incomplete status.

[prasoon92]

On the dashboard for the 950 Jobs the filter is working properly but i need to show the remaining 50 Jobs as incomplete status. we have one link table in the model where we have all the jobs based on order date from source and facts table. If i could show these 50 Unplanned jobs then also the requirement will meet.

[Anonymous]

I have a table where I also have nulls. when i add a new column as calculated column like

if (isnull(field), 'N/A', field) I can select the N/A.

maybe this gives you an idea. in my case i have a table with details

[poojashribanger]

Try below code:

LOAD

    tex,

    sales,

    product

FROM [lib://AttachedFiles/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1) where not isnull(sales);

[Anonymous]

you cannot select 'N/A'. Unfortunately, you only see it, but no Chance to select

[prasoon92]

That's what  we cannot select N/A even through calvulated dimension.

This is what i am trying now in the load script:

LinkTable:

Load distinct

Jname&'|' &Odate as %key,

Jname&'|' &Odate as %tempkey

Jname,Odate,Jobstatus

resident fact;

Concatenate (LinkTable)

Load distinct

Jname&'|' &Odate as %key ,

Jname&'|' &Odate as %tempkey2

Jname,odate, 'Unplanned' as Jobstatus

Resident source

where not exists (%tempkey ,%tempkey2);

But Not getting desired result.

Please help. Can we create additional columns in qlik like I created above for jobstatus in above table?

[Anonymous]

you load the link table without a condition. So you load all data and for each data a key %tempkey is created.

The following exist does not find any further keys

what is your condition? jobstatus?

so extend your Link table

by

where isnull(Jobstatus)  if the field contains null

[prasoon92]

I don't have jobstatus in source table. In fact table I have job status for each job. I am giving unplanned for all the jobs in source then taking only those which are not in fact table. Will that not work