Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasoon92
Contributor
Contributor

select in field action in qlikview

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable

okay, I understood

the your script is okay, but I am confused about your exists. Do you get an error?

because the right Parameter %tempkey2 does not exist in your source table and will be created

I would expect

where not exists(%tempkey, Jname&'|' &Odate)

View solution in original post

11 Replies
Anonymous
Not applicable

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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
Not applicable

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
Creator II
Creator II

Try below code:


LOAD

    tex,

    sales,

    product

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

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

Anonymous
Not applicable

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

prasoon92
Contributor
Contributor
Author

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
Not applicable

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
Contributor
Contributor
Author

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