topic Re: How to create 3 values from one date field variable in QlikView

How to create 3 values from one date field variable

Wed, 25 Nov 2020 16:16:04 GMT

Hi All ,

Would it be possible to create 3 values from one date filed.what I am expecting here

If date field is null show as Pend , if date field is not null show as complete and want to show if date field is total as Recieved

This is expecting

if(isnull(date ),'Pend',if(not Is null(date),'complete ',if(isnull(date)and if (is not null(date),'received') as newfield

I am able to getting two values that Pend ,complete

How to get two values total as received

Please guide me how to do it

Re: How to create 3 values from one date field variable

sunny_talwar — Wed, 15 Jun 2016 13:46:36 GMT

Your 3rd condition: if(isnull(date)and if (is not null(date),'received')

How can date be null and not null at the same time?

Re: How to create 3 values from one date field variable

marcus_malinow — Wed, 15 Jun 2016 13:48:41 GMT

It could be a quantum date

Re: How to create 3 values from one date field variable

syukyo_zhu — Wed, 15 Jun 2016 13:52:37 GMT

Hi,

Try this, but i'm not sur this is according your request

if(isnull(date ),'received-Pend','received-complete') as newfield

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 13:56:27 GMT

Orelse want to see total count as Received I need three values , null , not null and both count

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 13:57:54 GMT

I need two separate values and both count , three values I am expecting

Re: How to create 3 values from one date field variable

qlikviewwizard — Wed, 15 Jun 2016 14:20:20 GMT

Hi,

Try this.

if(isnull(date ),'Pending',if(not Is null(date),'complete ',if(isnull(date) and is not null(date),'received')

Re: How to create 3 values from one date field variable

Digvijay_Singh — Wed, 15 Jun 2016 14:22:13 GMT

How 'date' can be null and not null at the same time, I think third condition need to be explained better

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 15:09:55 GMT

I can see only 2 values which are Pend and complete ,not able to getting Received.i want to achieve pend ,complete and Received.Assume Received as both values total

Re: How to create 3 values from one date field variable

Anonymous — Wed, 15 Jun 2016 15:10:44 GMT

Hi,

You ask "How to get two values total as received". Do you mean that you have 2 date fields ?

Otherwise, like already mentioned, a date cannot be null() and not null() at the same time.

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 15:12:54 GMT

the third condition is here I am expecting both total count like : pend--Null,complet---not null and received ----both count(pend & complete)

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 15:23:37 GMT

I know how to get two values total as received using in a another condition, but my requirement is, in a same condition I want see pend(null), complete(not null) and received(both count), assume I want to see three values as a dimension. I am not sure is that possible or not

Re: How to create 3 values from one date field variable

sunny_talwar — Wed, 15 Jun 2016 15:30:55 GMT

May be create a link table using your existing table:

LINKTABLE:

LOAD 'Pending' as Flag,

UniqueIdentifierFieldHere

From YourFactTable

Where Len(Trim(date)) = 0;

Concatenate(LINKTABLE)

LOAD 'Complete' as Flag,

UniqueIdentifierFieldHere

From YourFactTable

Where Len(Trim(date)) > 0;

Concatenate(LINKTABLE)

LOAD 'Received' as Flag,

UniqueIdentifierFieldHere

From YourFactTable;

Re: How to create 3 values from one date field variable

syukyo_zhu — Wed, 15 Jun 2016 15:34:52 GMT

Yes you can do it

count({<newfield={'received*'} >}mesure) for both

count({<newfield={'received-Pend'} >}mesure) for one

count({<newfield={'received-complete'} >}mesure) for one

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 15:50:56 GMT

Pending missing for your logic (above chart)

Expecting Chart below ,this chart don't have dimension, three individual expressions ,requirement is need to show as dimension

Re: How to create 3 values from one date field variable

Wed, 15 Jun 2016 15:57:38 GMT

I need to show as a dimension, I already applied three individual expression ,look at the below one. let me tell you my req user want to select bar individually but he wont able to select because of there is no any dimension ,now if I want to create dimension user able to select it isn't it

Re: How to create 3 values from one date field variable

sunny_talwar — Wed, 15 Jun 2016 16:20:50 GMT

Can you share the script you used?

Re: How to create 3 values from one date field variable

syukyo_zhu — Thu, 16 Jun 2016 07:28:16 GMT

OK i got it.

So i think your should do it on your script.

load *,

if(isnull(date ),'Pend',if(not Is null(date),'complete ') as newfield

from your table;

concatenate

load *,

'received' as newfield

from your table;

And then you can use newfield as dimension in your chart.

hope helpful

Re: How to create 3 values from one date field variable

Kushal_Chawda — Thu, 16 Jun 2016 07:58:36 GMT

First create the Flag in scrip

LOAD *,

if(len(trim(Date))=0 or isnull(Date),'Pending',

if(len(trim(Date))>0 or not isnull(Date),'Completed')) as Flag

Create Bar chart

Dimension:

Valuelist('Received','Completed','Pending')

Expression:

=pick(match(Valuelist('Received','Completed','Pending'),'Received','Completed','Pending'),

Sum({<Flag={'Pending','Completed'}>} Amount),

Sum({<Flag={'Completed'}>} Amount),

Sum({<Flag={'Pending'}>} Amount) )

Re: How to create 3 values from one date field variable

Thu, 16 Jun 2016 08:36:21 GMT

I am able to get three values on bars but I want to select each bar that is my requirement.this logic wont work out isn't it I mean as per selesctions