topic Re: Remove null data in load in QlikView

Remove null data in load

Anonymous — Tue, 14 May 2013 10:46:10 GMT

I need to remove data where records show null for a spesific field. I cannot do this in the sql query because I need it in yet another load

I have a code lookin like the one below.

I started out with one load, but have read several postes that susggest that I should do a noconcatenate load as well.

In the Stopp load I add a where clause and have tried 3 different methods.

But still my table box from Stopp will show trucks that have null.

Stopp1:

LOAD

truck & '-' & min_lic AS KEY3,

,truck as truck3

.min_lic as min_lic3

.load_time as StoppTime3

,IsNull(truck) as NULLID;

SQL select bla bla.....

NoConcatenate

Stopp:

Load

KEY3 AS KEY,

truck3 as truck,

min_lic3 as min_lic,

StoppTime3 as StoppTime,

NULLID;

RESIDENT Stopp1

WHERE

not IsNull(truck3); // TEST 3

//NULLID;= 0; // TEST 2

//len([truck3])>0; // TEST 1

DROP Table Stopp1:

Dan

Re: Remove null data in load

giakoum — Tue, 14 May 2013 10:49:44 GMT

This should be adequate:

WHERE

not IsNull(truck3);

Are you sure the values are null?

Try

WHERE

truck3 <> '';

Re: Remove null data in load

Anonymous — Tue, 14 May 2013 10:50:47 GMT

I use the Len(FeildName)>0 method and i usually works for me.

TableBox's can be quite misleading as keys common across fields will dupliacte rows and make it look like there are more nulls tha there are.

Try recreating the fields in the datamodel that you want to check on, or using a chart with expressions testing for only(FieldName)

Jonathan

Re: Remove null data in load

Anonymous — Tue, 14 May 2013 12:03:51 GMT

Tried truck <> '' but still I get records that I do not want. The two lines in the midle should not be there.

Re: Remove null data in load

Tue, 14 May 2013 12:08:29 GMT

where nullid is not null

or select supress null values in your straight table.

Re: Remove null data in load

Anonymous — Tue, 14 May 2013 12:10:07 GMT

Looking at the image you see my data. What I need is to rule out the two lines in the left table(pivot) that does not get a stop date and time.

The join is done between KEY and I wnat to display the StoppTime value.

In the pic below you see two records with - instead of a valid timestamp, that is because the flow have been stopped for some reason and thus I do not want to import them to my stop table at all, for this view.

Re: Remove null data in load

Tue, 14 May 2013 12:12:58 GMT

try to promote or demote your dimension, then in dimension select field and check supress null value.

I think if you take Key in expression then use supress null value in dimension.

Re: Remove null data in load

kumarnatarajan — Tue, 14 May 2013 12:21:15 GMT

Hi,

Some Records have Space so use below mentioned Condition.

EX:

WHERE Len(Trim(truck3))>0;

Re: Remove null data in load

giakoum — Tue, 14 May 2013 12:44:16 GMT

Please give some sample data, and it will be fixed in no time.

Just make sure you give the source also, so that we can reload.

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 05:55:50 GMT

I have not attached code examples before, but tried it no. Hope I have done it correctly

Dan

Re: Remove null data in load

narender123 — Wed, 15 May 2013 06:20:01 GMT

Hi,

Try this

WHERE

truck3 <> Null();

Regards:

Narender

Re: Remove null data in load

giakoum — Wed, 15 May 2013 06:27:36 GMT

no, no attachment is there

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 06:29:51 GMT

I added it at the top level in my original post. Sorry

Dan

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 06:33:32 GMT

I have tried

truck3 <> Null();

truck3 <> '-';

truck3 <> '';

None of them works

I still get those empty truck records

Dan

Re: Remove null data in load

Wed, 15 May 2013 06:38:36 GMT

On your bottom table box, select on key Omit rows then null. Then they will not show

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 06:44:18 GMT

That would leave them out of the view yes, but not from the table i self. I would need it to be removed in the load. They should not be included at all.

Dan

Re: Remove null data in load

Wed, 15 May 2013 06:58:42 GMT

Hey, just a try

take a text object with expression '=ord(truck2)' hope fully you should get some numeric value by clicking on any of the null values shown in the above screeshot.

if you get '0' then try giving a where clause as 'WHERE truck2<>chr(0)'

lets see what happens....

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 07:12:10 GMT

I get the number 49 regardles of what value I click on for the field truck2.

Btw, I then tested WHERE truck2<>chr(49).

No happy ending

Dan

Re: Remove null data in load

Sokkorn — Wed, 15 May 2013 07:50:16 GMT

Hi Dan,

Any join table in load script? Anyhow, just let a try: Where truck3 <> Null() or truck3 <> '-' or truck3 <> '' or Len(truck3)>0;

Regards,

Sokkorn

Re: Remove null data in load

Anonymous — Wed, 15 May 2013 08:06:21 GMT

Hi Sokkorn

There is a join between the two tables on the Key field.

If I rename hte Key to Key2 and thus break the join your where clause seems to work.

But I then have no connection between the two tables and I cannot get the stopdate field into my main table.

Dan