topic Re: Null values and QVD in QlikView

Null values and QVD

qlikviewaf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Null values and QVD

prat1507 — Tue, 20 Jun 2017 14:09:27 GMT

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

Re: Null values and QVD

qlikviewaf — Tue, 20 Jun 2017 14:22:39 GMT

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

Re: Null values and QVD

sunny_talwar — Tue, 20 Jun 2017 14:29:05 GMT

May be do this while loading

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

...

Re: Null values and QVD

qlikviewaf — Tue, 20 Jun 2017 14:33:39 GMT

not working

Re: Null values and QVD

sunny_talwar — Tue, 20 Jun 2017 14:36:37 GMT

Can you share the script where you tried this?

Re: Null values and QVD

qlikviewaf — Tue, 20 Jun 2017 14:37:56 GMT

here we are

Re: Null values and QVD

sunny_talwar — Tue, 20 Jun 2017 14:40:28 GMT

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.

Re: Null values and QVD

qlikviewaf — Tue, 20 Jun 2017 14:50:02 GMT

Yes,

there's a join using Short_Item with another table

Re: Null values and QVD

sunny_talwar — Tue, 20 Jun 2017 14:52:16 GMT

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;

Re: Null values and QVD

qlikviewaf — Tue, 20 Jun 2017 14:58:32 GMT

for all users - i tested it using Debug and was not working.

Reloading all script is working.

I just used: LOAD If(Len(Trim(Item_Category)) = 0, 'NA', Item_Category) as Item_Category,