Null values and QVD

[qlikviewaf]

Hi Guys,

i'm struggling with my app.

as you can see i have some null-values for the "Item_Category" field - i'm using QVD.

I really don't know how i can manage it in order to filter it on set-analysis.

Any Advise?

Thank you

[prat1507]

If you want to choose only NULL values you should use:

Sum(  {$ <Item_Category= {$(=list_of_groups_with_NULL)}> } Amount  )

where list_of_groups_with_NULL is generated like this:

Concat( DISTINCT TOTAL If(IsNull(Item_Category), Chr(39) & Item_Category& Chr(39)), ', ')

Now you can just use this statement with "-" to choose all Item_Category without NULLs:

Sum(  {$ <Item_Category -  = {$(=list_of_groups_with_NULL)}> } Amount  )

Regards

Pratyush

[qlikviewaf]

I would like to have a value into the Item_category like NA for null values.

As you can see now i have only SMP

[sunny_talwar · Accepted solution]

May be do this while loading

LOAD If(Len(Trim(Item_Category)) = 0, 'NA', Item_Category) as Item_Category,

           ...

[qlikviewaf]

not working

[sunny_talwar]

Can you share the script where you tried this?

[qlikviewaf]

here we are

[sunny_talwar]

That is the only script you run and there are no joins or anything taking place after this? The reason I ask this is because a join can create null values which won't get fixed here. Those will be fixed in a resident table after the join has completed.

[qlikviewaf]

Yes,

there's a join using Short_Item with another table

[sunny_talwar]

Share your complete script in text format or try this

Table1:

LOAD....

FROM ...

Join (Table1)

LOAD ....

FROM ...

FinalTable:

LOAD ....,

     If(Len(Trim(Item_Category)) = 0, 'NA', Item_Category) as Item_Category

Resident Table1;

DROP Table Table1;