topic Re: how o remove null dates in date column in QlikView

how o remove null dates in date column

Anonymous — Wed, 20 Apr 2016 09:38:16 GMT

Hi Team,

i have one date dimension column and one progress metric column

metric was created in variable

let us ex:

progress=if(no.ofusers<>o,(no.ofusers/Totalusers),0)

using these two columns i want to create "DATEofvalues"

condition is:

if particular date have some value then i want to show that date in "DATEofvalues"Column.

other wise i dont want to see that date in "DATEofvalues"Column.

ex:if i have a date like 4/20/2016 12:00:00 pm.if this date having any "progrees" metric value then i want to display that date

if that date dont have any values then i dont want to display the date in "DATEofvalues"Column

note:

for getting "DATEofvalues" i dont want use any charts or any objects and i dont want to do in Script.i need that "DATEofvalues" columnn in variable level.if we need to change in script level then i need "date" coumn and i need "DATEofvalues" both

regards,

Brahma.

Re: how o remove null dates in date column

Chanty4u — Wed, 20 Apr 2016 09:46:47 GMT

chk dis

write in script

SET NullValue = 'N';

NullAsValue *; // You can give the listed field names instead of * (All fields)

Remove null data in load

Re: how o remove null dates in date column

nico_ilog — Wed, 20 Apr 2016 10:06:55 GMT

Hi Bobby,

I would do it in the script.

YourTable:

LOAD

DateField as yourDateField,

if(progress = 0,DateField) as DATEofvalues //Dates that have values

....

Then you have it setup from the start.

Nico

Re: how o remove null dates in date column

Anonymous — Wed, 20 Apr 2016 10:15:59 GMT

Hi sureshqv

Good one but if i already mentioned in posting..if we need to do any thing in edit script level i need "DATEofvalues"Column

as well as remaining columns like date,no.ofusers,Totalusers with out any filters

note:

in basic column we are getting from date base like date,no.ofusers,Totalusers i dont want to use any where condition or any filter conditions because these columns i need to some other charts as well.

Regards,

Brahma.

Re: how o remove null dates in date column

nico_ilog — Wed, 20 Apr 2016 10:21:15 GMT

Bobby,

Give this a shot and see.

DateField as yourDateField,

if(progress = 0,DateField) as DATEofvalues //Dates that have values

You will keep all existing fields as loaded, just another field added to see DATEofvalues.

Basic change.

Re: how o remove null dates in date column

Anonymous — Wed, 20 Apr 2016 10:45:21 GMT

Hi,
nico_ilog

sorry to say this that script level change is not impacted. i tried above your suggestions

when i was checking "DATEofvalues" column in list box i am able to see all columns

regards,

Bobc

Re: how o remove null dates in date column

nico_ilog — Wed, 20 Apr 2016 10:50:31 GMT

Is that not the desired result?

Perhaps i am mis-interpreting your question?

Re: how o remove null dates in date column

Anonymous — Wed, 20 Apr 2016 10:58:59 GMT

nope

let me give a small example:

see bellow screen shot i want to create DATEofvalues...if progress equal to zero then i dont want that date or

if progress is not equal to zero then i need that particular Date

so when i am using DATEofvalues column in any chart or any calculation i want bellow 2 days only

because that particular date have some progress vales

Re: how o remove null dates in date column

nico_ilog — Wed, 20 Apr 2016 11:13:41 GMT

Ahhhh..

Use this.

YourTable:

LOAD

DateField as yourDateField,

if(progress = 0,DateField) as DATE_with_noProgress //Dates with no progress

if(progress > 0,DateField) as DATE_with_Progress // Dates with progress

I mis-interpreted your question.

Have a go. Should work

Re: how o remove null dates in date column

Anil_Babu_Samineni — Wed, 20 Apr 2016 11:27:07 GMT

Nico, I just tried this. Can you please elaborate for me. So that, I can understand which you written on your application. Thanks

Re: how o remove null dates in date column

nico_ilog — Wed, 20 Apr 2016 11:53:34 GMT

The logic is this:

LOAD

DateField as yourDateField, <-- LOAD the Date field, un-touched so that you have ALL the dates

if(progress = 0,DateField) as DATE_with_noProgress <-- LOAD the Date fields, where the corresponding progress columns are 0 ONLY. Then you have all the dates in the DATE_with_noProgress dimension. The other dates, that do have progress are no included on the field.

if(progress > 0,DateField) as DATE_with_Progress <-- LOAD the Date fields, where the corresponding progress columns are more 0 ONLY. Then you have all the dates in the DATE_with_Progress dimension. The other dates, that do not have progress are no included on the field.

See attached qvw for example.

Re: how o remove null dates in date column

Anonymous — Thu, 21 Apr 2016 07:32:09 GMT

Excellent Nico Manro Oberholzer

but i need some more clarification on "DATE_with_noProgress".please check attached QVW file.

so i need to remove null date DATE_with_noProgress. (null date means helighted in above screen shot)

Note:

in chart properties -->we can we "supress null","supress missing" some thing like i dont want to use that option.

as of i know "supress null","supress missing" some thing is also available in chart properties-->Dimentation-->so i dont want to use this options as well

so with out using above options how to show the "DATE_with_noProgress" column in pivots and charts with out "NULL"

let me know if you need any information.

Regards,

Bobs

Re: how o remove null dates in date column

nico_ilog — Thu, 21 Apr 2016 08:12:16 GMT

Hi Bob,

I think the suppress NULL in the dimension properties was created for this exact use case.

Nevertheless, See attached.

I changed the way in which the Data is displayed into the chart, expression side.

Let me know if it works for you.

Otherwise i would recommend building in FLAGS into the load script.

Nico

Re: how o remove null dates in date column

Anonymous — Thu, 21 Apr 2016 09:05:36 GMT

Nope

nico_ilog

let me give some idea :

EX:

if(PROGRESS = 0,DATE) as DATE_with_no_Progress,

in above condition if PROGRESS = 0 then it wil display DATEs....if PROGRESS not equal to Zero,then it will display "zero" or '-'...some thing like bellow you can see in screen shot.so i want to restrict or delete or truncate the '-' value in column level only

note:

in table or chart level i dont want use "supress" or "null missing" or any hiding null options

regards,

bBBy

Re: how o remove null dates in date column

nico_ilog — Thu, 21 Apr 2016 09:20:27 GMT

I Honestly do not think that it's possible.

I tried adding this into the load but still does not work.

The only other thing you can try and do is in the [Presentation] tab, where it says use the following symbol to represent NULL values and it has "-".

Remove that symbol and leave it blank then it will not show a character.

Nico

Re: how o remove null dates in date column

Anonymous — Thu, 21 Apr 2016 09:32:30 GMT

nope ..see already i mentioned i dont want do any thing in charts or pivot level.

i want to put that restrict in column Level only ...

is there any other options Nico Manro Oberholzer????

Regards,

Bobb

Re: how o remove null dates in date column

nico_ilog — Thu, 21 Apr 2016 09:57:06 GMT

I am not too sure.

Depending on the business case of your QV implementation, i would try a different approach to the problem as seen in the attached.

Other than that, i'm out of ideas.

Nico

Re: how o remove null dates in date column

Anonymous — Thu, 21 Apr 2016 10:16:27 GMT

any way some time we will definitely get answer.thanks Nico Manro Oberholzer for spending your time on my issues and you gave good ideas as well

topic Re: how o remove null dates in date column in QlikView

how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Hi,nico_ilog

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Re: how o remove null dates in date column

Hi,
nico_ilog