Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating Feilds

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);


14 Replies
Not applicable
Author

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 ?

Not applicable
Author

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

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

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Jaivik,

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

Thanks,

Jason