topic Creating Feilds in QlikView

Creating Feilds

Mon, 28 May 2012 11:02:20 GMT

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

Creating Feilds

Jason_Michaelides — Mon, 28 May 2012 11:06:31 GMT

Not sure I understand correctly, but you could try:

Workorders_Table:

LOAD

[Incident ID],

[Owned by Group],

[WO Number],

IF(IsNull([WO Number],'Has not WO','Has WO') as WO_Filter

FROM

$(FactPath)BI_Workorders.QVD

(qvd);

Hope this helps,

Jason

Creating Feilds

Mon, 28 May 2012 11:53:01 GMT

it comes up with an error on the line :

IF(IsNull([WO Number] , 'Has not WO','Has WO') as WO_Filter

where the brackets don't equal but also the ' , ' is flagged red ???

Creating Feilds

Jason_Michaelides — Mon, 28 May 2012 12:16:29 GMT

Slight typo - sorry!

IF(IsNull([WO Number]),'Has not WO','Has WO') as WO_Filter

Creating Feilds

Mon, 28 May 2012 12:42:19 GMT

ok thanks this works but the same as the code which i sent,

it still only gives one option so that the WO_Filter you can only select 'Has WO' :S ?

Creating Feilds

Jason_Michaelides — Mon, 28 May 2012 12:50:26 GMT

So when and why do you want 'Has not WO'?

Creating Feilds

Mon, 28 May 2012 12:56:27 GMT

i want the option of having the WO_Filter to have to options which are ' Has WO ' and 'No WO' so that the information can be filtered accordinaly

Creating Feilds

magavi_framsteg — Mon, 28 May 2012 13:31:14 GMT

Hi.

Then it might just be so, that all rows in your data source indeed does have [WO Number] != null?

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Creating Feilds

Jason_Michaelides — Mon, 28 May 2012 15:10:21 GMT

As Magnus has said - my code checks for a [WO Number] and where there is one returns 'Has WO' otherwise it returns 'Has not WO'. Should the logic be checking something different?

Re: Creating Feilds

magavi_framsteg — Mon, 28 May 2012 15:17:51 GMT

Hi.

There are no rows without WO Nbr, is there?

You will only get to the else part of the if statement IF, and only if, the row has no WO Nbr.

Creating Feilds

Tue, 29 May 2012 07:20:56 GMT

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 ?

Creating Feilds

Tue, 29 May 2012 07:52:30 GMT

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

Re: Creating Feilds

magavi_framsteg — Tue, 29 May 2012 08:20:54 GMT

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.

Creating Feilds

Anonymous — Thu, 31 May 2012 10:29:08 GMT

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

Creating Feilds

Jason_Michaelides — Thu, 07 Jun 2012 07:05:47 GMT

Jaivik,

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

Thanks,

Jason