Discussion Board for collaboration related to QlikView App Development.
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?
Solved! Go to Solution.
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)
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.
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.
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
Try below code:
(ooxml, embedded labels, table is Sheet1) where not isnull(sales);
That's what we cannot select N/A even through calvulated dimension.
This is what i am trying now in the load script:
Jname&'|' &Odate as %key,
Jname&'|' &Odate as %tempkey
Jname&'|' &Odate as %key ,
Jname&'|' &Odate as %tempkey2
Jname,odate, 'Unplanned' as Jobstatus
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?
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
where isnull(Jobstatus) if the field contains null
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