Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
help4qv123
Creator II
Creator II

Fetch only Date

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

1 Solution

Accepted Solutions
nirmalraj_kj
Partner - Contributor III
Partner - Contributor III

Find the attachment hope it will helps you

View solution in original post

12 Replies
vardhancse
Specialist III
Specialist III

Hi,

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

Date(Floor())

month()

Year()

functions.

Anonymous
Not applicable

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

like

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

thanks

BKC

help4qv123
Creator II
Creator II
Author

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

PrashantSangle

Hi,

If it contain only date then use

count(DISTINCT Date)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
help4qv123
Creator II
Creator II
Author

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

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable

You can use

Count(DISTINCT FieldName)

anbu1984
Master III
Master III

Script

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

     @2

FROM

sampl.xls

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

Try this expr in Text box

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

nirmalraj_kj
Partner - Contributor III
Partner - Contributor III

Find the attachment hope it will helps you