topic Re: Fetch only Date in QlikView

Fetch only Date

help4qv123 — Fri, 29 May 2015 05:52:51 GMT

Hi,

'I have a database in which one of the column has dates and some duplicate values or records in it. i need to show only count(date).

plz check the attachmnt

Re: Fetch only Date

vardhancse — Fri, 29 May 2015 06:08:38 GMT

Hi,

in order to get date/month.year from a field is possible using

Date(Floor())

month()

Year()

functions.

Re: Fetch only Date

Anonymous — Fri, 29 May 2015 06:09:54 GMT

Here you have many problems :

1. It is not recommended to have junk data ('dddd','NA')in source itself (Need to talk your source team to clean the data )

2. Your column is not in date format in excel itself.

Solution :

if you know the junk values you can use if statement and filter the data

if (match(Date,'dddd','asd','N/A','etc')=0,' ',date) as CorrectDate

thanks

BKC

Re: Fetch only Date

help4qv123 — Fri, 29 May 2015 06:40:48 GMT

in database its in date format. can we solve if it is in date format.

Re: Fetch only Date

PrashantSangle — Fri, 29 May 2015 06:43:38 GMT

Hi,

If it contain only date then use

count(DISTINCT Date)

Regards

Re: Fetch only Date

help4qv123 — Fri, 29 May 2015 06:54:17 GMT

It doesn't work. it contain date with some other values like alphabetic, numeric...etc...i need the count of only dates.

Re: Fetch only Date

PrashantSangle — Fri, 29 May 2015 06:59:23 GMT

Hi,

Then you have to create flag in script which identify which is proper date and which is not date.

and use that flag in your set analysis.

like count({<Flag-={"Not Date"}>}DISTINCT Date)

Regards

Re: Fetch only Date

Fri, 29 May 2015 07:02:20 GMT

You can use

Count(DISTINCT FieldName)

Re: Fetch only Date

anbu1984 — Fri, 29 May 2015 07:05:42 GMT

Script

LOAD Date#(@1,'DD-MMM-YYYY') As Date,

FROM

sampl.xls

(biff, no labels, table is Sheet1$);

Try this expr in Text box

=Count(If(IsNum( Date),1))

Re: Fetch only Date

nirmalraj_kj — Fri, 29 May 2015 07:15:43 GMT

Find the attachment hope it will helps you

Re: Fetch only Date

prajapatiamar38 — Fri, 29 May 2015 07:17:33 GMT

Hi....

Please find the attachment.

I have used Flag it is coming right now.

Re: Fetch only Date

maxgro — Fri, 29 May 2015 07:39:17 GMT

count(if(isnum(date#(date, 'DD-MMM-YYYY')), distinct date))

Re: Fetch only Date

help4qv123 — Fri, 29 May 2015 09:46:43 GMT

yes , but how can we create a flag?