Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how o remove null dates in date column

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

Capture.PNG

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.

17 Replies
Chanty4u
MVP
MVP

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

nico_ilog
Partner - Creator II
Partner - Creator II

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

Anonymous
Not applicable
Author

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.

nico_ilog
Partner - Creator II
Partner - Creator II

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.

Anonymous
Not applicable
Author

Hi,

nico_ilog
Partner - Creator II
Partner - Creator II

Is that not the desired result?

Perhaps i am mis-interpreting your question?

Anonymous
Not applicable
Author

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

nico_ilog
Partner - Creator II
Partner - Creator II

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

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful