Creating Feilds

[#1]

I have the following code :

qualify *;
unqualify [Incident ID], WO_Filter;

Workorders_Table:
LOAD [Incident ID],
     [Owned by Group],
     [WO Number],
     'Has WO' as WO_Filter
    
    
FROM
$(FactPath)BI_Workorders.QVD
(qvd);

Which creates a feild WO_Filter which has the option of 'Has WO'

How would i change this so that the WO_Filter has the option of 'Has WO' and 'Has not WO' ???

BWSBpJ3yqualify *;
unqualify [Incident ID], WO_Filter;

Workorders_Table:
LOAD [Incident ID],
     [Owned by Group],
     [WO Number],
     'Has WO' as WO_Filter
    
    
FROM
$(FactPath)BI_Workorders.QVD
(qvd);
qualify *;
unqualify [Incident ID], WO_Filter;

Workorders_Table:
LOAD [Incident ID],
     [Owned by Group],
     [WO Number],
     'Has WO' as WO_Filter
    
    
FROM
$(FactPath)BI_Workorders.QVD
(qvd);

[#2]

there is as I've created a table with ,

Incident No. , WO No. ,WO_Filter

and they all populate with there results and for the last section 'WO_Filter' has 'Has WO' or ' - ' where the ' - ' should be the else part of the statement ?

[#3]

Hi,

Can you share some of your original data in this qvd file? => $(FactPath)BI_Workorders.QVD

Is it something like this?

[Incident ID],[Owned by Group], [WO Number]

1, owner1,wo1

2,owner2,wo2

...

What Magnus mean is that for every row, you have something inside this [WO Number] field(ie wo1, wo2). That is why you always get  'Has WO'. Try to delete some value in [WO Number]. ie make this table like this:

[Incident ID],[Owned by Group], [WO Number]

1, owner1

2, owner2,wo2

...

You will see 'Has not NO' in your WO_Filter.

Regards,

Xue Bin

[magavi_framsteg]

I'm not sure exactly how you mean, and I need to see your data source.

QlikTech has adviced us (partner firm) not to use IsNull() and instead use len() in combination with trim() to check if len(trim(field)) >= 1 because IsNull returns different results on x86 and x64.

With my code below, you can see that WO_Filter2 does not catch the null value, but len(trim(field)) does.

The code was created on QV v11 SR1 but gives the same results on QV v8.5, QV v9, QV v10 and QV v11.

See attached file test_isnull.qvw.

tmp:
LOAD * INLINE [
    Incident ID, Owned by Group, WO Number
    1, 1, 1
    2, 1, 2
    3, 2, 2
    4, 2
];
MyTable:
load
          [Incident ID]
          ,[Owned by Group]
          ,[WO Number]
          ,if (len(trim([WO Number])) >= 1, 'Has WO', 'Has not WO') as WO_Filter // Use len() and trim() instead of isnull() because isnull() gives different results on x86 and x64
          ,if (isnull([WO Number]), 'Has not WO', 'Has WO') as WO_Filter2 // Use len() and trim() instead of isnull() because isnull() gives different results on x86 and x64
resident tmp;
drop table tmp;

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

[Anonymous]

Function IsNull () checks for null but not for blank.  Try to replace
isnull([WO Number])
with
len(trim([WO Number]))=0

[Jason_Michaelides]

Jaivik,

If you have what you need, please close this thread by marking a correct answer so it appears solved to others.

Thanks,

Jason