topic Re: Handling NULL Values in QlikView

Handling NULL Values

Sat, 14 Dec 2013 10:23:40 GMT

Hi friend,

Im facing a problem that im having the Excel sheet, which having NULL value also. and i want to replace he null value into previous value (last non empty) value (LIKE prevmember in tableu/SSAS) .

i tried a expression

alt(max(amount),above(max(amount),1))

it failed if i select the particular date / field , and it display only to the next null value if 2 or more field is null then it is not through this result

Please help in this issue

For your reference i attach the excel and my Qlikview also.

Re: Handling NULL Values

Gysbert_Wassenaar — Sat, 14 Dec 2013 10:50:53 GMT

If the order of the data in the excel file is already in the correct order and you could use this load statement:

LOAD Dt,
    aa,
    bb,
    alt(amount,peek(amount)) as amount,
    day,
    month,
    year
FROM
comm101886.xls
(biff, embedded labels, table is Sheet1$);

If the null handling should also take aa and bb into account then you would need to reorder the data first:

Temp:
LOAD Dt, 
     aa, 
     bb, 
     amount,
     day, 
     month, 
     year
FROM
comm101886.xls
(biff, embedded labels, table is Sheet1$);

Result:
NoConcatenate
LOAD Dt, 
     aa, 
     bb, 
     if(previous(aa)=aa and previous(bb)=bb,alt(amount,peek(amount)),amount) as amount,
     day, 
     month, 
     year
Resident Temp
order by aa,bb,Dt;

Drop table Temp;

Re: Handling NULL Values

Sat, 14 Dec 2013 11:00:08 GMT

Hi Gysbert,

Thanks for reply ,

Actually i can't touch script level. because it is link to date table and that date table link to nearly 13 Tables , whenever there is some record in another table, this table affect by NULL for that case only i'm checking..

and i can't touch other table structure also.

Thanks in advance

Re: Handling NULL Values

Mon, 16 Dec 2013 07:30:41 GMT

Any Reply from any one

Re: Handling NULL Values

Thu, 02 Jan 2014 05:08:29 GMT

Please Reply in this issue

Re: Handling NULL Values

Anonymous — Thu, 02 Jan 2014 05:22:26 GMT

You can use obove function with pivot table ,

Re: Handling NULL Values

qlikpahadi07 — Thu, 02 Jan 2014 06:20:35 GMT

Hi Sundaramoorthi,

As per my experience I will also suggest the same as gwassenaar had mentioned which is actually get your data rectified from the point you are pulling it.

Your scenario >13 Tables then I will say you must do that and for practice it will take not more than few minutes.

Here is what I suggest for you:

I am not sure but you might be aware of Qlikview have an option where we can replace Null data with above values in wizard at the time of pulling data in Transformation Tab like below or you can use gwassenaarsolution too.

The point is for every tables (>13) you can simply make a Resident of all individual table and drop the previous one as already mentioned by gwassenaar

I don't thing this will make trouble to you.Only you need to be refreshed and careful while doing this change.

once it is corrected from Script then you don't need to bother for rest JUST CLICK ON RELOAD as you have not changed any Logic .

ELSE for every Chart with respect of Logic/Dimensions you have to create different logic to aggr the data in front end and if any change request then this approach will trouble the Developer my friend .....If I be at your side will never do that.

Hope this might be Helpful

Re: Re: Handling NULL Values

Fri, 03 Jan 2014 10:04:22 GMT

Hi Pahadi,

Thanks for mailing

Please find my attachment for the excel, this is how my data are stored

In this,

Take field aa(field) = AAAA and bb(Field) = 1111

1st jan i have Amount 10$ , after that there is no transaction till 19th

if my user select aa =AAAA and bb= 1111 for 3rd jan (any date after 1st to 18th)

then it must show amount is 10$,

lly , for the same if he try to select on 25 or 26th (any date after 19th to 30th)

it must display 2000,

after 31st (any date even its today)

it must display 7000

i think u may catch my point

if i do it in Script level the size may increase. Right now we are dealing with 1TB/day Records . so we want optimum solutions for this issue.

Re: Handling NULL Values

Sat, 04 Jan 2014 19:20:23 GMT

Hi nithin,

i tried but it's not working see my above Reply to Pahadi

Re: Handling NULL Values

Sat, 04 Jan 2014 22:38:38 GMT

Hi Sundaramoorthi, I'm not sure if I'm understanding your problem. In any case a proper way to fill NULL values during load is using something like this:

IF (len(trim(amount))=0, Peek('new_amount',-1), amount) AS new_amount

In other words, if amount is null, peek the previous value and rename the field as "new_amount". I guess that playing around with the IF condition (to check "aa" or "bb" values) you may get your desired result.

Hope this helps

Re: Handling NULL Values

qlikpahadi07 — Sun, 05 Jan 2014 05:22:51 GMT

Hi Sundar,

well in your case i don't think it's easy to achieve from Front end like your case

"Take field aa(field) = AAAA and bb(Field) = 1111"

if i am not wrong this will also go for other fields too ...

as per my Experience its possible through Back end or you can create a Master Table with all Items Listed from Day 1

and make right/ Outer join with you Fact Table.

Once we have a Dimensions with every Date then those Above Functions can easily work as you see you can create a Record with Loops in Backend only; Front end is for display with few tricks but if Records are not there in your applications how can you display that in Front end.

I can suggest to work some in Back end (Script) not matter the data is in TB you can try optimized that .

Or wait for any other to reply

Re: Re: Handling NULL Values

sasikanth — Sun, 05 Jan 2014 07:29:38 GMT

Hello moorthi

Go through the fallowing manual contains full explanation how to handle NULL values

Thanks

sasi@