Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
prasoon92
New 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
Partner
Partner

Re: select in field action in qlikview

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
Partner
Partner

Re: select in field action in qlikview

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

Re: select in field action in qlikview

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

Re: select in field action in qlikview

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.

Partner
Partner

Re: select in field action in qlikview

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

Re: select in field action in qlikview

Try below code:


LOAD

    tex,

    sales,

    product

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

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

Partner
Partner

Re: select in field action in qlikview

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

prasoon92
New Contributor

Re: select in field action in qlikview

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?



Partner
Partner

Re: select in field action in qlikview

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

Re: select in field action in qlikview

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