Handling NULL Values

[#1]

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.

[Gysbert_Wassenaar]

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;

[#3]

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

[#4]

Any Reply  from any one

[#5]

Please Reply in this issue

[Anonymous]

You can use obove function with pivot table ,

[qlikpahadi07]

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

[#8]

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.

[#9]

Hi nithin,

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

[#10]

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